Where condition question

[jsccorps]

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)

 

[Duane Hookom]

"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.

 

[jsccorps]

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.

"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

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)

 

[John Vinson]

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)

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

 

[Duane Hookom]

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

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.

"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

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)

 

[jsccorps]

Thanks John

That solved it.

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)

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