DLookUp Syntax

[DS]

This DLookUp Statement is giving me problems. The are all number
fields except the last field. I think that it's the last field which is
an F that is the culprit.


Me.TxtGroup = Nz(DLookup("GroupID", "tblInfoGroup", _
"tblInfoGroup.TerminalID = " & Forms!frmOrderScreen!TxtOSStation & " " &
_"AND tblInfoGroup.DayID = " & Forms!frmOrderScreen!TxtOSDay & " " & _
"AND tblInfoGroup.MenuID = " & Forms!frmOrderScreen!TxtOSMenu & " " & _
"AND tblInfoGroup.MenuCatID = " & Forms!frmOrderScreen!TxtOSSection & "
" & _
"AND tblInfoGroup.ItemID = " & Forms!frmOrderScreen!TxtOSItem & " " & _
"AND tblInfoGroup.GroupLevel = " & Forms!frmOrderScreenForce!TxtLevel &
" " & _"AND tblInfoGroup.GroupAction & " = F), 0)

Thanks
DS

 

[fredg]

On Tue, 12 Dec 2006 16:35:15 -0500, DS wrote:


If GroupAction is Text datatype, then you need to surround the F with
quotes "F" or in this case single quotes 'F'.

..... "AND tblInfoGroup.GroupAction = 'F')

 

[Damon Heron]

" " & _"AND tblInfoGroup.GroupAction = " & "' F '"), 0)
(that's " then ' F ' then ")
HTH
Damon

 

[DS]

" " & _"AND tblInfoGroup.GroupAction = " & "' F '"), 0)
(that's " then ' F ' then ")
HTH
Damon

Here's what I have, it still returns Null or 0. If I take the F
statement out I get the correct result, with it it returns a zero.

Thnaks
DS


Me.TxtGroup = Nz(DLookup("GroupID", "tblInfoGroup", _
"tblInfoGroup.TerminalID = " & Forms!Form5!TxtOSStation & " " & _
"And tblInfoGroup.DayID = " & Forms!Form5!TxtOSDay & " " & _
"And tblInfoGroup.MenuID = " & Forms!Form5!TxtOSMenu & " " & _
"And tblInfoGroup.MenuCatID = " & Forms!Form5!TxtOSSection & " " & _
"And tblInfoGroup.ItemID = " & Forms!Form5!TxtOSItem & " " & _
"And tblInfoGroup.GroupLevel = " & Forms!Form5!TxtLevel & " " & _
"AND tblInfoGroup.GroupAction = " & "' F '"), 0)

 

[Guest]

Should it be F or F with a space either side, because you have F with a space
either side...

Damian.

 

[DS]

You have too many "&" and " " !!!!

Your first code or your first post should look like this:

Me.TxtGroup = Nz(DLookup("GroupID", "tblInfoGroup", _
"tblInfoGroup.TerminalID = " & Forms!frmOrderScreen!TxtOSStation & _
" AND tblInfoGroup.DayID = " & Forms!frmOrderScreen!TxtOSDay & _
" AND tblInfoGroup.MenuID = " & Forms!frmOrderScreen!TxtOSMenu & _
" AND tblInfoGroup.MenuCatID = " & Forms!frmOrderScreen!TxtOSSection & _
" AND tblInfoGroup.ItemID = " & Forms!frmOrderScreen!TxtOSItem & _
" AND tblInfoGroup.GroupLevel = " & Forms!frmOrderScreenForce!TxtLevel & _
" AND tblInfoGroup.GroupAction = 'F'"), 0)


Then you changed the form name, so the code should look like this:

Me.TxtGroup = Nz(DLookup("GroupID", "tblInfoGroup", _
"tblInfoGroup.TerminalID = " & Forms!Form5!TxtOSStation & _
" And tblInfoGroup.DayID = " & Forms!Form5!TxtOSDay & _
" And tblInfoGroup.MenuID = " & Forms!Form5!TxtOSMenu & _
" And tblInfoGroup.MenuCatID = " & Forms!Form5!TxtOSSection & _
" And tblInfoGroup.ItemID = " & Forms!Form5!TxtOSItem & _
" And tblInfoGroup.GroupLevel = " & Forms!Form5!TxtLevel & _
" AND tblInfoGroup.GroupAction = 'F'"), 0)


Put the space (& " " &) in front of the "AND....." . It really cleans up
your code.

Since the "F" is a constant here, just put it in the text. That is 'F' " as
in

single quote, F, single quote, double quote

I switched form names so that it would be neater in the post, but....

Your code worked GREAT!

Thanks, the F was driving me crazy! I've done numbers but the F was a
hard one. Once again Thank you very much!
DS