Syntax error

  • Thread starter Thread starter Steve S
  • Start date Start date
S

Steve S

I am getting a syntax error when the following executes and I can not figure
out where the problem lies. I need to NZ() BOTH birthday fields since either
or both could be null.

If (Not IsNull(DLookup("[Last Name]", "[Twirlers]", _
"[Last Name]= '"" & Me.[Last Name] & ""' And [Birthday] = '"" &
Me.[Birthday] & ""')) Then....


HELP
 
Assuming [Birthday] is a date/time field, you need to use # as a delimiter
(and you need to ensure that the date's in a format Access will recognize
correctly).

If (Not IsNull(DLookup("[Last Name]", "[Twirlers]", _
"[Last Name]= '"" & Me.[Last Name] & _
""' And [Birthday] = " &
Format(Me.[Birthday], "\#yyyy\-mm\-dd\#"))) Then....

I'm not sure what you mean about needing to "NZ() BOTH birthday fields". For
one thing, having two birthday fields in a table is probably indicative that
your design is suboptimal.
 
I'm assuming that [Birthday] is of the datatype Date/Time. If so you need to
be using

#'s

to delimit it, not quotes.

Have a look here for an example

http://www.mvps.org/access/general/gen0018.htm

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
 
Sorry, Doug! Didn't mean to step on your toes!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
 
I am getting a syntax error when the following executes and I can not figure
out where the problem lies. I need to NZ() BOTH birthday fields since either
or both could be null.
Click to expand...

Well, let's parse out your quotes. It looks like they are the problem. Putting
blanks between them for readability...

If (Not IsNull(DLookup("[Last Name]", "[Twirlers]", _
"[Last Name]= ' " " & Me.[Last Name] & " " ' And [Birthday] = ' " " &
Me.[Birthday] & " " ')) Then....

I'm not sure what you were intending but you certainly won't get it! The third
argument of DLOOKUP should be a text string which evaluates to a legal SQL
WHERE clause (without the WHERE). Since lastnames may well contain apostrophes
(O'Brien for instance), it's best to use " as a string delimiter; and if
birthday is a Date/Time field you need # as a delimiter. You want the final
string to resemble

[Last Name] = "O'Brien" AND [Birthday] = #5/21/1999#

So to put it together from its components, you need to use two consecutive
doublequotes in the string constant to represent one doublequote:

"[Last Name]=""" & Me.[Last Name] & """ And [Birthday] = #" &
Me.[Birthday] & "#")) Then....

That is, you're concatenatign the pieces

[Last Name] = "
<whatever is in the Last Name control on your form>
" And [Birthday] = #
<whatever is in the birthday control>
#

If Birthday is of some other datatype, use ' instead of # for Text, and no
delimiter at all for Number.
 
thanks for the quick reply. One 'birthday' field is the valye of a textbox
on the form(Me.Birthday) and the other is the value in the table
[Twirlers].[Birthday]

Since either or both can be enpty or null I need to have something like
NZ([birthday],0) so a null field will be tested as 0 (zero).

How would I insert the NZ() function into the SQL String??

Douglas J. Steele said:
Assuming [Birthday] is a date/time field, you need to use # as a delimiter
(and you need to ensure that the date's in a format Access will recognize
correctly).

If (Not IsNull(DLookup("[Last Name]", "[Twirlers]", _
"[Last Name]= '"" & Me.[Last Name] & _
""' And [Birthday] = " &
Format(Me.[Birthday], "\#yyyy\-mm\-dd\#"))) Then....

I'm not sure what you mean about needing to "NZ() BOTH birthday fields". For
one thing, having two birthday fields in a table is probably indicative that
your design is suboptimal.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Steve S said:
I am getting a syntax error when the following executes and I can not
figure
out where the problem lies. I need to NZ() BOTH birthday fields since
either
or both could be null.

If (Not IsNull(DLookup("[Last Name]", "[Twirlers]", _
"[Last Name]= '"" & Me.[Last Name] & ""' And [Birthday] = '"" &
Me.[Birthday] & ""')) Then....


HELP
Click to expand...
Click to expand...
 
Are you saying that you want to match a Null in the textbox to a Null in the
table? That seems a very unusual requirement...

If (Not IsNull(DLookup("[Last Name]", "[Twirlers]", _
"[Last Name]= '"" & Me.[Last Name] & _
""' And Nz([Birthday], #1/1/100#) = " &
Format(Nz(Me.[Birthday], #1/1/100#), "\#yyyy\-mm\-dd\#"))) Then....


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Steve S said:
thanks for the quick reply. One 'birthday' field is the valye of a
textbox
on the form(Me.Birthday) and the other is the value in the table
[Twirlers].[Birthday]

Since either or both can be enpty or null I need to have something like
NZ([birthday],0) so a null field will be tested as 0 (zero).

How would I insert the NZ() function into the SQL String??

Douglas J. Steele said:
Assuming [Birthday] is a date/time field, you need to use # as a
delimiter
(and you need to ensure that the date's in a format Access will recognize
correctly).

If (Not IsNull(DLookup("[Last Name]", "[Twirlers]", _
"[Last Name]= '"" & Me.[Last Name] & _
""' And [Birthday] = " &
Format(Me.[Birthday], "\#yyyy\-mm\-dd\#"))) Then....

I'm not sure what you mean about needing to "NZ() BOTH birthday fields".
For
one thing, having two birthday fields in a table is probably indicative
that
your design is suboptimal.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Steve S said:
I am getting a syntax error when the following executes and I can not
figure
out where the problem lies. I need to NZ() BOTH birthday fields since
either
or both could be null.

If (Not IsNull(DLookup("[Last Name]", "[Twirlers]", _
"[Last Name]= '"" & Me.[Last Name] & ""' And [Birthday] = '"" &
Me.[Birthday] & ""')) Then....


HELP
Click to expand...
Click to expand...
Click to expand...
 
This works -

If Not IsNull(DLookup("[Last Name]", "[Twirlers]", "[Last Name]= """ &
Me.[Last Name] & """ And [Birthday] = #" & Format(Me.[Birthday],"mm/dd/yy")
& "#")) Then ...

So does this -

If Not IsNull(DLookup("[Last Name]", "[Twirlers]", "[Last Name]= """ &
Me.[Last Name] & """ And [Birthday] = #" & Format(Me.[Birthday],"mm/dd/yy")
& "#")) Then ...

You must take care with " and ' (your '"" was part of your problem) and know
that "" resolves to " ( so "" followed by " is equivanlent to ' followed by "
) and use # for dates.

Cheers ...
 
Oops ... So does this should have been -

If Not IsNull(DLookup("[Last Name]", "[Twirlers]", "[Last Name]= '" &
Me.[Last Name] & "' And [Birthday] = #" & Format(Me.[Birthday],"mm/dd/yy")
& "#")) Then ...

Cheers ...
 
Back
Top