Where condition question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Why is it that the following statements work when run separately.

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where [Date
of Sales]< #" & [Date of Sales] & "# ", dbOpenDynaset)

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where
[SalesRep] = & [SalesRep]", dbOpenDynaset)

However, does not work when the conditions are combined:

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where [Date
of Sales]< #" & [Date of Sales] & "# And [SalesRep]= & [SalesRep]",
dbOpenDynaset)
 
"does not work" leaves a lot of room for interpretation. Can't you do
better?
This
[SalesRep]= & [SalesRep]
should possibly be:
[SalesRep]= """ & [SalesRep] & """"
This depends on if the field is text or numeric.
 
The SalesRep field is numeric. When run it does not return any records.
(works fine when the conditions are run separately)

However, if I run a parameter query with criteria <[Enter Date of Sales] and
[Enter Sales Rep], the appropriate records are returned.

So there must be problem with the Where condition.

Duane Hookom said:
"does not work" leaves a lot of room for interpretation. Can't you do
better?
This
[SalesRep]= & [SalesRep]
should possibly be:
[SalesRep]= """ & [SalesRep] & """"
This depends on if the field is text or numeric.
--
Duane Hookom
MS Access MVP


jsccorps said:
Why is it that the following statements work when run separately.

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where [Date
of Sales]< #" & [Date of Sales] & "# ", dbOpenDynaset)

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where
[SalesRep] = & [SalesRep]", dbOpenDynaset)

However, does not work when the conditions are combined:

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where [Date
of Sales]< #" & [Date of Sales] & "# And [SalesRep]= & [SalesRep]",
dbOpenDynaset)
Click to expand...
Click to expand...
 
Why is it that the following statements work when run separately.

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where [Date
of Sales]< #" & [Date of Sales] & "# ", dbOpenDynaset)

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where
[SalesRep] = & [SalesRep]", dbOpenDynaset)

However, does not work when the conditions are combined:

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where [Date
of Sales]< #" & [Date of Sales] & "# And [SalesRep]= & [SalesRep]",
dbOpenDynaset)
Click to expand...

Sounds like you need to move the SalesRep being used as a criterion
OUT of the quotes:

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where
[Date of Sales]< #" & [Date of Sales] & "# And [SalesRep]= " &
[SalesRep], dbOpenDynaset)

assuming that you have a control on your form named SalesRep.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Dim strSQL as String
strSQL = "Select * From [Transactions] " & _
"Where [Date of Sales]< #" & [Date of Sales] & _
"# And [SalesRep] =" & [SalesRep]
Debug.Print strSQL
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If this doesn't work open the debug window (Ctrl+G) and paste the sql into a
new, blank query.
--
Duane Hookom
MS Access MVP


jsccorps said:
The SalesRep field is numeric. When run it does not return any records.
(works fine when the conditions are run separately)

However, if I run a parameter query with criteria <[Enter Date of Sales] and
[Enter Sales Rep], the appropriate records are returned.

So there must be problem with the Where condition.

Duane Hookom said:
"does not work" leaves a lot of room for interpretation. Can't you do
better?
This
[SalesRep]= & [SalesRep]
should possibly be:
[SalesRep]= """ & [SalesRep] & """"
This depends on if the field is text or numeric.
--
Duane Hookom
MS Access MVP


jsccorps said:
Why is it that the following statements work when run separately.

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where [Date
of Sales]< #" & [Date of Sales] & "# ", dbOpenDynaset)

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where
[SalesRep] = & [SalesRep]", dbOpenDynaset)

However, does not work when the conditions are combined:

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where [Date
of Sales]< #" & [Date of Sales] & "# And [SalesRep]= & [SalesRep]",
dbOpenDynaset)
Click to expand...
Click to expand...
Click to expand...
 
Thanks John

That solved it.

John Vinson said:
Why is it that the following statements work when run separately.

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where [Date
of Sales]< #" & [Date of Sales] & "# ", dbOpenDynaset)

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where
[SalesRep] = & [SalesRep]", dbOpenDynaset)

However, does not work when the conditions are combined:

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where [Date
of Sales]< #" & [Date of Sales] & "# And [SalesRep]= & [SalesRep]",
dbOpenDynaset)
Click to expand...

Sounds like you need to move the SalesRep being used as a criterion
OUT of the quotes:

Set rs = CurrentDb.OpenRecordset("Select * From [Transactions] Where
[Date of Sales]< #" & [Date of Sales] & "# And [SalesRep]= " &
[SalesRep], dbOpenDynaset)

assuming that you have a control on your form named SalesRep.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top