Attempting to use SQL in a module (1 Viewer)

[harrisw48]

I am getting errors with the statement below

sql = "select * from [tablename] where [fieldname]="criteria""

set dbase.openrecordset(SQL)

It appears to be the sql statement that is giving the error.
How can I enter criteria with the "" double quotes

Thanks in advance

 

[Fizzio]

Single quotes will normally suffice except if your field may consist of strings with an apostrophe eg O'Connor etc

In that case, try

sql = "select * from [tablename] where [fieldname]="& """ & criteria & """

but generally using Like is better for string comparisons.

However, if the field is numric, drop the quotes ie

sql = "select * from [tablename] where [fieldname]= "& criteria

 

[harrisw48]

How would this look if the criteria was a variable

ie another field in another table

Thanks

 

[Fizzio]

This gets a little more complex but why exactly do you need to feed a query criteria with a value from another table?
This makes me think that you are trying to force it which generally indicates a non-optimal query design or data structure.

 

[jgc31]

The correct syntax is either

where [ fieldname ] = " & """ & criteria & """"

or

where [ fieldname ] = " & " ' " & criteria & " ' "

 

[harrisw48]

Ok ill try and give you more info

I have 2 tables in access

one holds data
one holds critera (counties)

i want to write a module which basically runs down the counties table picking out the top 300 records from the data table in order of employee size, highest first and then add the 300 records to a third table.

The module will run down the counties table selecting the top 300 records from each county and add them to the third table.

Hope this helps a bit more

How do I go about selecting the top 300 by employee size

Your help is much appreciated.

 

[Fizzio]

Providing you have a robunt link between your data and lookup tables, you could do this with a looping recordset to
a) loop through all the counties
b) use the county produced by this to power the criteria in your query
c) Use and append query to add the data returned to the new table.

Rather than code this from scratch, there are lots of examples within this forum to draw upon. Get back if you cannot find anything suitable.

jgc31 - what about numerics then

 

[harrisw48]

What would the SQL look like to run the process.

If you can point me in the direction of the threads you mention that would be great

 

[Mile-O]

The correct syntax is either

where [ fieldname ] = " & """ & criteria & """"

or

where [ fieldname ] = " & " ' " & criteria & " ' "

or, for short

" where [ fieldname ] = """ & criteria & """"

As for that second example - bear in mind that you are adding two spaces to the literal string, one at either end.

"where [ fieldname ] = "'" & criteria & "'"

Just to finish:

For numbers:

" where [ fieldname ] = " & criteria

For dates:

" where [ fieldname ] = #" & criteria & "#"

 

[Fizzio]

What would the SQL look like to run the process.

If you can point me in the direction of the threads you mention that would be great

Use the search and see what you come across - best way to learn new hints, tricks and tips. Get back if you are really stuck.