Dlookup syntax error #3 Please :-)))

[Alain]

Hi to all again,

Can anybody let me know on how to fix this syntax error on the Dlookup
function, Access is pointing the error at the (#" right after the AND, no
matter what I do I am getting the error syntax elsewhere in the criteria

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch] = " & num
& ") _
AND (#" & Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"


Many thanks again

Alain

 

[Guest]

I am fairly new to VB, but I seem to have trouble with line breaks in the
middle of a string. I would put:
& _
") AND (#"
not
& ") _
AND (#"

 

[Roger Carlson]

The Format function will convert the date into a string. But because you
have the #, the interpreter thinks it should be a date. You also have a
problem with parenthases. I'd try it without the Format function at all and
remove the inner parenthases:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch] = " & num
& _
" AND #" & omtdate & "# Between [from] And [to])"

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Alain]

Hi Roger,

I tried your suggestion and , yes again, still getting a syntax error. it
looks like this function hates me ))

Alain

The Format function will convert the date into a string. But because you
have the #, the interpreter thinks it should be a date. You also have a
problem with parenthases. I'd try it without the Format function at all
and
remove the inner parenthases:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch] = " &
num
& _
" AND #" & omtdate & "# Between [from] And [to])"

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi to all again,

Can anybody let me know on how to fix this syntax error on the Dlookup
function, Access is pointing the error at the (#" right after the AND, no
matter what I do I am getting the error syntax elsewhere in the criteria

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch] = " & num
& ") _
AND (#" & Format(omtdate, "mm\/dd\/yyyy") & "# Between [from] And [to])"


Many thanks again

Alain

 

[John Vinson]

Hi Roger,

I tried your suggestion and , yes again, still getting a syntax error. it
looks like this function hates me ))

Alain

The Format function will convert the date into a string. But because you
have the #, the interpreter thinks it should be a date. You also have a
problem with parenthases. I'd try it without the Format function at all
and
remove the inner parenthases:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch] = " &
num
& _
" AND #" & omtdate & "# Between [from] And [to])"

I think Roger may have misplaced a parenthesis. Try

" AND #" & omtdate & "# Between [from] And [to]")

This assumes that your table has fields named omtdate, From and To.
Otherwise - where are From and To coming from?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Alain]

Thanks John,

I will try this solution although I recheck all the parentheses yesterday
and I tought I place the missing one at the right place.
My table contain fields From and To and the omtdate is a variable containing
a manual input

Alain

Hi Roger,

I tried your suggestion and , yes again, still getting a syntax error. it
looks like this function hates me ))

Alain

The Format function will convert the date into a string. But because
you
have the #, the interpreter thinks it should be a date. You also have a
problem with parenthases. I'd try it without the Format function at all
and
remove the inner parenthases:

temp2 = DLookup("[Annual OMT]", "Budget BasicRental", "[idbranch] = " &
num
& _
" AND #" & omtdate & "# Between [from] And [to])"

I think Roger may have misplaced a parenthesis. Try

" AND #" & omtdate & "# Between [from] And [to]")

This assumes that your table has fields named omtdate, From and To.
Otherwise - where are From and To coming from?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thanks John,

I will try this solution although I recheck all the parentheses yesterday
and I tought I place the missing one at the right place.
My table contain fields From and To and the omtdate is a variable containing
a manual input

What's the datatype of omtdate in the Dim statement? Note that it
should be formatted with mm/dd/yyyy or an unambiguous format such as
yyyy-mmm-dd.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps