Dlookup multiple criteria not working

R

Risto85

Registered User.
Local time
Today, 16:17
Joined
Feb 25, 2015
Messages
11
Hello

I have a small problem with dlookup multiple criteria. Vba code looks like this:

Code: 
 Label34.Caption = DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")

This gives following error:
Syntax error in number in query expression '[Date_Added]=4.02.2015 And [Shift] = '2'.

[Shift] column is a numeric field.



best regards,
Risto
 
So:

Code: 
"[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")

You have a field called Date_Added, and a treating it numerically. Yet the output is 4.02.2015 , which looks like a Text field as it's not a date (slashes not dots make dates!). Then you say Shift is a Numeric field, yet you are treating it as Text.

Could it be that you are after something like this:

Code: 
"[Date_Added] = """ & Me.Text27 & """ And [Shift] = " & Me.Text29")

Or, if Date_Added is actually a date, then you need the date delimiter, the #.

Code: 
"[Date_Added] = #" & Me.Text27 & "# And [Shift] = " & Me.Text29")

EDIT TO ADD: Get some better names for your controls. Text27 and Text29 are only going to cause you confusion down the line.
 
In vba, dates need to be in US format and wrapped in #
and ' wrapping (or "") is needed for text, not numbers

Try:
"[Date_Added]= #" & Me.Text27 & "# And [Shift] = " & Me.Text29 & "")

And make sure text27 is in US format... after which I am sure you will give the two text boxes proper names, keeping default names is asking for maintenance problems down the line.
 
[Date_Added] is date field.
With both of your solutions I get Compile error: Syntax error.


Mile-O said:
So:

Code: 
"[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")
You have a field called Date_Added, and a treating it numerically. Yet the output is 4.02.2015 , which looks like a Text field as it's not a date (slashes not dots make dates!). Then you say Shift is a Numeric field, yet you are treating it as Text.

Could it be that you are after something like this:

Code: 
"[Date_Added] = """ & Me.Text27 & """ And [Shift] = " & Me.Text29")
Or, if Date_Added is actually a date, then you need the date delimiter, the #.

Code: 
"[Date_Added] = #" & Me.Text27 & "# And [Shift] = " & Me.Text29")
EDIT TO ADD: Get some better names for your controls. Text27 and Text29 are only going to cause you confusion down the line.
Click to expand...
 
Mile-o is missing an & and a " ... and/or has a surplus "

Did you try my version?
 
I tried your version says:

Syntax error in date in query expression '[Date_Added] = #4.02.2015# And [Shift] = '.

How can I change textbox to us format?



namliam said:
Mile-o is missing an & and a " ... and/or has a surplus "

Did you try my version?
Click to expand...
 
Its a textbox, you enter the data in it right? try entering 02/04/2015, if only just to see if its working :)

Alternatively change the textbox to a date picker and use format to force it.
Code: 
"[Date_Added]= #" & format(Me.Text27, "MM/DD/YYYY") & "# And [Shift] = " & Me.Text29 & "")
 
Yes, it's a textbox. I entered date using slashes but it automatically converted it to dots.

When I try
Code: 
"[Date_Added]= #" & format(Me.Text27, "MM/DD/YYYY") & "# And [Shift] = " & Me.Text29 & "")
then I get: Syntax error in date in query expression '[Date_Added] = #02.04.2015# And [Shift] = '.


I also tried your code without hashtags then I get:
Syntax error in number in query expression '[Date_Added] = 02.04.2015 And [Shift] = '.

PS! The the standard date format in our country (Estonia) is dd.mm.yyyy
and In my table the [Shift] field is Long integer, could that be also the case?


namliam said:
Its a textbox, you enter the data in it right? try entering 02/04/2015, if only just to see if its working :)

Alternatively change the textbox to a date picker and use format to force it.
Code: 
"[Date_Added]= #" & format(Me.Text27, "MM/DD/YYYY") & "# And [Shift] = " & Me.Text29 & "")
Click to expand...
 
Try entering the dlookup in the immediate window
If your date is converted to dots, then it is NOT a text box, but a date field
A text box would leave the slashes untouched.

Something along the lines of
?DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= #02/04/2015# And [Shift] = 1")
 
In immidiate window
Code: 
?DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= #02/04/2015# And [Shift] = 1")
gives the correct result, which is 12




namliam said:
Try entering the dlookup in the immediate window
If your date is converted to dots, then it is NOT a text box, but a date field
A text box would leave the slashes untouched.

Something along the lines of
?DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= #02/04/2015# And [Shift] = 1")
Click to expand...
 
Risto85 said:
In immidiate window
Code: 
?DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= #02/04/2015# And [Shift] = 1")
gives the correct result, which is 12
Click to expand...
Given this works, the problem lies in either value being entered in the Text25 or Text27 (did you change those names yet???!!!!)
 
Names are changed and still Syntax error in number in query expression '[Date_Added] = 02.04.2015 And [Shift] = '.

Both textbox fields are now un-formatted.


Code: 
  Label34.Caption = DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= " & Format(Me.Date1, "MM/DD/YYYY") & " And [Shift] = " & Me.Shift & "")


namliam said:
Given this works, the problem lies in either value being entered in the Text25 or Text27 (did you change those names yet???!!!!)
Click to expand...
 
Can you post a screenshot of your table '249_1_CHours's design view?

Also, what on earth does that table name imply?
 
249_1_Chours = 249_1 is machine name and Chours means Check hours. It's a query. Main data is kept in table 249_1_data. Screenshots for both of them can be found website: lean.planet.ee/risto



Mile-O said:
Can you post a screenshot of your table '249_1_CHours's design view?

Also, what on earth does that table name imply?
Click to expand...
 
Reading through all this again the issue is likely lying with this dot-based date represenation.

While you say that . is the Estonian format (over /), we are seeing that / works in the Immediate Window.

People type in 02.04.2015, which is generating a Syntax Error as Access doesn't recognise this format for a date.

Not ideal, but I'm wondering if we can turn this to a string, replace the dots, and put back in US format.

Code: 
"[Date_Added]= " & Format(CDate(Replace(Me.Date1,".","/")), "MM/DD/YYYY") & " And [Shift] = " & Me.Shift)


Unrelated, but having seen that table (and you saying that it relates solely to a single machine) then it seems likely that you may want to investigate normlization when it's convenient.
 
I tried, but still Syntax error in number in query expression '[Date_Added] = 02.04.2015 And [Shift] = '.



Mile-O said:
Reading through all this again the issue is likely lying with this dot-based date represenation.

While you say that . is the Estonian format (over /), we are seeing that / works in the Immediate Window.

People type in 02.04.2015, which is generating a Syntax Error as Access doesn't recognise this format for a date.

Not ideal, but I'm wondering if we can turn this to a string, replace the dots, and put back in US format.

Code: 
"[Date_Added]= " & Format(CDate(Replace(Me.Date1,".","/")), "MM/DD/YYYY") & " And [Shift] = " & Me.Shift)
Unrelated, but having seen that table (and you saying that it relates solely to a single machine) then it seems likely that you may want to investigate normlization when it's convenient.
Click to expand...
 
Hmm, I forgot the # delimiters, although I would expect to see a Type Mismatch error rather than a Syntax Error.

This should fix that part:
Code: 
[Date_Added] = #" & Format(CDate(Replace(Me.Date1,".","/")), "MM/DD/YYYY") & "# And [Shift] = " & Me.Shift)

If it does, then the issue now appears to be centred on the Shift field. Certainly from your screenshot it's a Number field, so that format should be correct.
Perhaps add Debug.Print Me.Shift before the DLookup line.
 
Used your formula, but didn't work. Error is Syntax error in date in query expression '[Date_Added] = #02.04.2015# And [Shift] = '.

Debug.Print Me.Shift result 2
Debug.Print Me.Date1 result 4.02.2015


Mile-O said:
Hmm, I forgot the # delimiters, although I would expect to see a Type Mismatch error rather than a Syntax Error.

This should fix that part:
Code: 
[Date_Added] = #" & Format(CDate(Replace(Me.Date1,".","/")), "MM/DD/YYYY") & "# And [Shift] = " & Me.Shift)
If it does, then the issue now appears to be centred on the Shift field. Certainly from your screenshot it's a Number field, so that format should be correct.
Perhaps add Debug.Print Me.Shift before the DLookup line.
Click to expand...
 
You must log in or register to reply here.

Similar threads

AsMok
Solved dlookup multi criteria
Replies
4
Views
366
AsMok
AsMok
C
Solved Combine both IIf and DLookup as Form control Source
Replies
2
Views
634
captbluefin
C
S
Solved Can I use Dlookup/DMIN lookups within VBA where part of the criteria is looking for a 'null' value in a field?
Replies
8
Views
902
June7
June7
josephbupe
Error - data type mismatch in criteria expression
Replies
10
Views
1,339
josephbupe
josephbupe
A
DLOOKUP with multiple AND, and OR Criteria
Replies
2
Views
1,512
astrodude2183
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom