Valid Query?

  • Thread starter Thread starter Vivista Eastbourne
  • Start date Start date
V

Vivista Eastbourne

I tried the following query in my vba:

STRSQL = lstSelectFrom >= txtSearchParameter11 & " AND " & lstSelectFrom] <
& txtSearchParameter21 & ";"
DoCmd.RunSQL STRSQL

but nothing happens, any ideas why this would not be valid?

lstSelectFrom is a listbox
txtSearchParameter11 and txtSearchParameter21 are the date range boxes

Thanks
 
well, if that's the entire SQL string, and if you copied it directly from
your code module, you have two problems. first, it's not a complete SQL
statement, and second, it's not a complete string. suggest you build the
query in query design view first, then change the SQL View and copy the SQL
statement, and paste that into the code window. then you need to put the
statement into strings, and properly concatenate them. use the following
example as a guide:

STRSQL = "SELECT FirstName, LastName, BirthDate, " _
& "EmpCode FROM tblEmployees WHERE FirstName = '" _
& Me!txtFirstName & "' And Birthdate = #" _
& Me!txtBirthDate & "# And EmpCode = " & Me!txtCode

in the above example, the FirstName field is a Text data type (in the
table) - note the reference to the txtFirstName control on the form is
surrounded by single quotes. the BirthDate field is a Date/Time data type,
so the control reference is surrounded by the # sign. and the EmpCode is a
Number data type, so that control reference is not surrounded by anything.

hth
 
Hi Tina,

Sorry, that was not the full code in my vba, missed a few (important) bits
off, oops.
I followed your suggestion of creating the query then viewing the SQL, which
showed me exactly what i needed, so Many Thanks

Andy

tina said:
well, if that's the entire SQL string, and if you copied it directly from
your code module, you have two problems. first, it's not a complete SQL
statement, and second, it's not a complete string. suggest you build the
query in query design view first, then change the SQL View and copy the SQL
statement, and paste that into the code window. then you need to put the
statement into strings, and properly concatenate them. use the following
example as a guide:

STRSQL = "SELECT FirstName, LastName, BirthDate, " _
& "EmpCode FROM tblEmployees WHERE FirstName = '" _
& Me!txtFirstName & "' And Birthdate = #" _
& Me!txtBirthDate & "# And EmpCode = " & Me!txtCode

in the above example, the FirstName field is a Text data type (in the
table) - note the reference to the txtFirstName control on the form is
surrounded by single quotes. the BirthDate field is a Date/Time data type,
so the control reference is surrounded by the # sign. and the EmpCode is a
Number data type, so that control reference is not surrounded by anything.

hth


Vivista Eastbourne said:
I tried the following query in my vba:

STRSQL = lstSelectFrom >= txtSearchParameter11 & " AND " &
Click to expand...
lstSelectFrom]
<
& txtSearchParameter21 & ";"
DoCmd.RunSQL STRSQL

but nothing happens, any ideas why this would not be valid?

lstSelectFrom is a listbox
txtSearchParameter11 and txtSearchParameter21 are the date range boxes

Thanks
Click to expand...
Click to expand...
 
you're welcome :)


Vivista Eastbourne said:
Hi Tina,

Sorry, that was not the full code in my vba, missed a few (important) bits
off, oops.
I followed your suggestion of creating the query then viewing the SQL, which
showed me exactly what i needed, so Many Thanks

Andy

tina said:
well, if that's the entire SQL string, and if you copied it directly from
your code module, you have two problems. first, it's not a complete SQL
statement, and second, it's not a complete string. suggest you build the
query in query design view first, then change the SQL View and copy the SQL
statement, and paste that into the code window. then you need to put the
statement into strings, and properly concatenate them. use the following
example as a guide:

STRSQL = "SELECT FirstName, LastName, BirthDate, " _
& "EmpCode FROM tblEmployees WHERE FirstName = '" _
& Me!txtFirstName & "' And Birthdate = #" _
& Me!txtBirthDate & "# And EmpCode = " & Me!txtCode

in the above example, the FirstName field is a Text data type (in the
table) - note the reference to the txtFirstName control on the form is
surrounded by single quotes. the BirthDate field is a Date/Time data type,
so the control reference is surrounded by the # sign. and the EmpCode is a
Number data type, so that control reference is not surrounded by anything.

hth


Vivista Eastbourne said:
I tried the following query in my vba:

STRSQL = lstSelectFrom >= txtSearchParameter11 & " AND " &
Click to expand...
lstSelectFrom]
<
& txtSearchParameter21 & ";"
DoCmd.RunSQL STRSQL

but nothing happens, any ideas why this would not be valid?

lstSelectFrom is a listbox
txtSearchParameter11 and txtSearchParameter21 are the date range boxes

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top