DLookUp keeps returning an error

[SepMan76]

I must have the syntax messed up or something. I have the following code in a Form:

intNum = DLookup("[Num]", "[qryDateExists]", "[Enter Date] = " & txtDateVal)

The query works great... but everytime I run the DLookUp, I get an error stating:

"Run-time error '3075':

Syntax error (missing operator) in query expression '[Enter Date] = 8/20/2001 6:53:27 PM'."

What am I missing?

 

[tomschafer]

I think you need to add quotes around your date string. Did you miss one in your post of the error message?

 

[SepMan76]

I have tried a number of different arrangements of the quotes... could you please post the appropriate syntax, outlining where you believe the quotes should go and if they are double or single quotes?


About the error message, the double quotes surround the entire error message, the single quotes are exactly as the error message reports.

thanks

 

[tomschafer]

I know I said quotes but this will also work...
intNum = DLookup("[Num]", "[qryDateExists]", "[Enter Date] = " & "#" & txtDateVal & "#&quot

Good Luck!

 

[SepMan76]

Well Pez... I get a different error now...

"Run-time error '2741':

The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'Enter date.'' "

When I run the query, a window comes up with "Enter date" and an input line to enter it. When entered, the query pulls exactly what I want.

Still trying over here... thanks again!

 

[tomschafer]

The query you are running is a parameter-query. Is this form the only thing the query is being used for?

 

[SepMan76]

Yes... the form is the only thing using it.

I could enter the full path of "txtDateVal" into the query and get this one to work (which is actually what I did) but then I am stuck on another, similar query.

It is one in which the parameter value is the counter within a loop. Can't hard code that.

understand my problem now? just didn't want to type all of that initially.

It is weird.. I have been messing with Access a while, but just started doing DLookups... I have many that work, but this one is different *shrug* "don't know why"

 

[tomschafer]

Are these two queries related to each other?
To solve your original problem I'd nuke the parameters because you are passing them in your code, they're cool if a user is running the query directly, otherwise I find them useless- just me though.
Can you post the SQL for the second query?

 

[SepMan76]

Here is the SQL:

SELECT [tblPSales].ID
FROM [tblPSales]
WHERE ((([tblPSales].StoreID)=[Enter ID]) AND ((Month([Date]))=Month([Enter Date])) AND ((Day([Date]))=Day([Enter Date])) AND ((Year([Date]))=Year([Enter Date])));

Store ID (under Enter ID) is a number 1 thru 9. I loop through enter the current date and the counter as the ID, but I get the same error.

DLookUp should handle this, right?

Something like:
intID = DLookUp("[ID]","[qryGetID]","[Enter ID] = " & i & " AND [Enter Date] = " & "#" & txtDateVal & "#&quot

Once again, thanks for all your help

 

[tomschafer]

Try forgetting about the query and do it all with dlookup like so ...

intID = DLookUp("[ID]","[tblPSales]","[StoreID]="& i & " and Date=" & "#" & txtDateVal & "#&quot