SQL statement generated by query - how to view

[Dirck]

To help figure out what I may be doing wrong in a query in
which I am inserting parameters, I would like to look at
the SQL statement which the query generates. Is there any
way I can do that, and if so, how?

 

[John Vinson]

To help figure out what I may be doing wrong in a query in
which I am inserting parameters, I would like to look at
the SQL statement which the query generates. Is there any
way I can do that, and if so, how?

Several. Simplest is to open the Query in design view and click the
dropdown arrow on the leftmost tool in the toolbar at the top of the
screen - the default looks like a datasheet, but one of the options is
SQL. Or, use the View menu option on the menubar and select SQL view.

If you're working on the query in VBA code, the Querydef object has a
SQL property.

 

[Dirck]

-----Original Message-----


Several. Simplest is to open the Query in design view and click the
dropdown arrow on the leftmost tool in the toolbar at the top of the
screen - the default looks like a datasheet, but one of the options is
SQL. Or, use the View menu option on the menubar and select SQL view.

If you're working on the query in VBA code, the Querydef object has a
SQL property.


.

Thanks.
Now I know the SQL statement is coming out correct, but I
still get a single, blank results line when I add one
criteria to my query. This criteria is looking in a
numeric field for a number greater than one which is typed
into a text box in the form which calls the query. I know
the variable is being set with a number by the form,
because I've had VBA print the variable in the immediate
window.

I have a feeling that the problem may be some
inconsistency between the number type and properties as
defined in the table, the text box in the calling form,
and the query. In use in the application, the number will
never get bigger than 99.999(it is actually a percentage,
[say, 7.875%] but doesn't have to be stored as one, since
it is never used in calculations; but it must be a number
of some kind for the selection [and possible sorting]
process). Do you have any suggestions as to what type of
number it should be, and with what properties in each of
the various places? Or might the blank result be the
result of something else?

 

[John Spencer (MVP)]

A guess, since you haven't given us a lot to look for. The following may be
obvious and you may have already taken care of this, but ...

Percentage is usually stored as a decimal value. That is
10% is stored as .1; 19.9% is stored as .199; etc.

So, are you looking for 10 when you really want to be looking for .1?

Also, be aware that decimals are often not stored exactly as the value you
expect. In Access, I would probably use the Currency field type as long as I
didn't need accuracy to more than 4 decimal places.

-----Original Message-----


Several. Simplest is to open the Query in design view and click the
dropdown arrow on the leftmost tool in the toolbar at the top of the
screen - the default looks like a datasheet, but one of the options is
SQL. Or, use the View menu option on the menubar and select SQL view.

If you're working on the query in VBA code, the Querydef object has a
SQL property.


.

Thanks.
Now I know the SQL statement is coming out correct, but I
still get a single, blank results line when I add one
criteria to my query. This criteria is looking in a
numeric field for a number greater than one which is typed
into a text box in the form which calls the query. I know
the variable is being set with a number by the form,
because I've had VBA print the variable in the immediate
window.

I have a feeling that the problem may be some
inconsistency between the number type and properties as
defined in the table, the text box in the calling form,
and the query. In use in the application, the number will
never get bigger than 99.999(it is actually a percentage,
[say, 7.875%] but doesn't have to be stored as one, since
it is never used in calculations; but it must be a number
of some kind for the selection [and possible sorting]
process). Do you have any suggestions as to what type of
number it should be, and with what properties in each of
the various places? Or might the blank result be the
result of something else?

 

[John Vinson]

In use in the application, the number will
never get bigger than 99.999(it is actually a percentage,
[say, 7.875%]

I think John Spencer is exactly right: if it's never bigger than
99.999 *and* it's a percentage, then it is always within the range
0.00 to 0.99999. The Percentage format multiplies the value by 100
before displaying it.

 

[Guest]

Thanks to both of you. It turns out that I needed
brackets around the word Forms and the Text Box name in
the Query Parameters window, even though it (the window)
had deleted the brackets (but kept the !) on other
parameters except around two-word objects. Once I did
that, any type of number worked.
The percent issue is a non-issue, because the program
doesn't know it's a percent; there's just in a label after
the text box, so you see 8.75% on the screen, and the
number is stored as 8.75. Thanks for letting me know what
the percent Format does, though.

-----Original Message-----

In use in the application, the number will
never get bigger than 99.999(it is actually a percentage,
[say, 7.875%]

I think John Spencer is exactly right: if it's never bigger than
99.999 *and* it's a percentage, then it is always within the range
0.00 to 0.99999. The Percentage format multiplies the value by 100
before displaying it.


.