Donate SIGN UP

Access Query and % tracking

Avatar Image
tpreece01 | 11:47 Wed 29th Jul 2009 | Computers
6 Answers
Hi all,

I've got an Access table with 2 fields.
[Name],[Consistency].
Consistency is a percentage. numbers are logged to two decimal places with the "%" sign displayed after it.

I'm trying to query all names with 100% consistency but don't know how to format it in the query wizard. (With % being a wildcard Access doesn't like it).

100.00%, "100.00%", #100.00%# etc all don't work...
Can anybody tell me how to make this query work?

Thanks for any help!
Gravatar

Answers

1 to 6 of 6rss feed

Avatar Image
What you have to always remember is that there is a distinction between the way a value is STORED in a database and the way it is DISPLAYED/FORMATTED.
When a field in Access is FORMATTED as a percentage, it is STORED as a number and DISPLAYED as that same NUMBER, but MULTIPLIED by 100 and with a % sign attached.
When you execute a QUERY using a selection...
09:42 Thu 30th Jul 2009
Question Author
Just a note - the table is a linked table form another db, so I can't remove the % sign from the data!
What is the type of the consistency field? test,number etc?
Sorry meant text not test
What you have to always remember is that there is a distinction between the way a value is STORED in a database and the way it is DISPLAYED/FORMATTED.
When a field in Access is FORMATTED as a percentage, it is STORED as a number and DISPLAYED as that same NUMBER, but MULTIPLIED by 100 and with a % sign attached.
When you execute a QUERY using a selection criterion, it DOES NOT take any notice of the format.
The upshot of all this is that your selection criterion must say Consistency=1, because that is how the DISPLAYED 100% is STORED.
TPreece
It's fine for you asking for help, but why don't you respond at least when people try to help you,
I imagine you as one of these people always in a hurry. I bet you've tried every avenue you can. Once you've got an answer you couldn't care less about the people who have tried to help.
The solution is everything.
Question Author
Vascop, really sorry for the lack of response.
Shortly after posting this question there was a major problem with another db at work that I had to deal with. It took a few days to get a backup restored and replace the missing data even after the backup.

Because of this I completely forgot I'd asked this question and have only just spotted all your replys. Again, apologies for the silence & I honestly do appreciate all the great answers you've provided.

1 to 6 of 6rss feed

Do you know the answer?

Access Query and % tracking

Answer Question >>

Related Questions

Sorry, we can't find any related questions. Try using the search bar at the top of the page to search for some keywords, or choose a topic and submit your own question.