Boolean criteria with DLookup

[bjaj]

Hi

How do I use a boolean criterian with the funktion DLookup ?

I know the syntax for strings, numeric and date as follows

For numerical values:
DLookup("FieldName" , "TableName" , "Criteria = n")

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #date#")

For booleans:
?????????????

best regards
Ronald

[Allen Browne]

No delimiters. Same as numbers, e.g.:
DLookup("FieldName" , "TableName" , "Criteria = False")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<bj**@vestas.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

How do I use a boolean criterian with the funktion DLookup ?

I know the syntax for strings, numeric and date as follows

For numerical values:
DLookup("FieldName" , "TableName" , "Criteria = n")

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #date#")

For booleans:
?????????????

best regards
Ronald

[bjaj]

Hi Allen

And thank you
It is working fine with only the boolean criteria, but when I add other
criteries I can't get it working.

Look at this:

Dim VareVar As String
Dim SerialNoVar As String
Dim DatoAfslVar As Date

If Not IsNull(VareVar = DLookup("VareNr", "T_FejlRecords",
"[VareNr] = '" & VareVar & "'" _
& "And [SerieNr] = '" &
SerialNoVar & "'" _
& "And [DatoAfsl] = #" &
DatoAfslVar & "#" _
& "And Repair = TRUE")) Then

This statement don't take notice of the "And Repair = TRUE"

What am I doing wrong ?

best regards
Ronald

[Br]

bj**@vestas.com wrote:

Hi Allen

And thank you
It is working fine with only the boolean criteria, but when I add
other criteries I can't get it working.

Look at this:

Dim VareVar As String
Dim SerialNoVar As String
Dim DatoAfslVar As Date

If Not IsNull(VareVar = DLookup("VareNr", "T_FejlRecords",
"[VareNr] = '" & VareVar & "'" _
& "And [SerieNr] = '" &
SerialNoVar & "'" _
& "And [DatoAfsl] = #" &
DatoAfslVar & "#" _
& "And Repair = TRUE")) Then

This statement don't take notice of the "And Repair = TRUE"

What am I doing wrong ?

best regards
Ronald

I always assign the criteria to a string variable so I can easily msgbox or
debug.print it to make sure the format is correct before passing it on to
the next part of the procedure. I also tend to put brackets arount each
criteria to make it more readable.
--
regards,

Br@dley

[Ted]

Looks like on the "And Repair = TRUE" you strayed from the format you
were using on the other comparisons. Try: " And [Repair]=TRUE" or " And
[Repair]=" & -1
bj**@vestas.com wrote:

Hi Allen

And thank you
It is working fine with only the boolean criteria, but when I add other
criteries I can't get it working.

Look at this:

Dim VareVar As String
Dim SerialNoVar As String
Dim DatoAfslVar As Date

If Not IsNull(VareVar = DLookup("VareNr", "T_FejlRecords",
"[VareNr] = '" & VareVar & "'" _
& "And [SerieNr] = '" &
SerialNoVar & "'" _
& "And [DatoAfsl] = #" &
DatoAfslVar & "#" _
& "And Repair = TRUE")) Then

This statement don't take notice of the "And Repair = TRUE"

What am I doing wrong ?

best regards
Ronald

[Bob Quintal]

bj**@vestas.com wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:

Hi Allen

And thank you
It is working fine with only the boolean criteria, but when I
add other criteries I can't get it working.

Look at this:

Dim VareVar As String
Dim SerialNoVar As String
Dim DatoAfslVar As Date

If Not IsNull(VareVar = DLookup("VareNr",
"T_FejlRecords",
"[VareNr] = '" & VareVar & "'" _
& "And [SerieNr] = '"
&
SerialNoVar & "'" _
& "And [DatoAfsl] =
#" &
DatoAfslVar & "#" _
& "And Repair =
TRUE")) Then

This statement don't take notice of the "And Repair = TRUE"

What am I doing wrong ?

best regards
Ronald

If you were to put the criteria into a variable and examine the
result in the immediate window, you would see that it returns

"[VareNr] = 'xxxxxxx'And [SerieNr] = 'yyy'And [DatoAfsl] =
#04-21-06#And Repair = TRUE"

You need to insert some spaces in front of your ANDs

You also need to verify that VareVar name You are trying to set
it and use it at the same time.

--
Bob Quintal

PA is y I've altered my email address.

[bjaj]

Thank You all

It's working now

Ronald