DLookup Brain fart

[Tony Vrolyk]

OK, I have done DLookups a lot but something is escaping me right now. I
cannot figure out the problem here so I thought a fresh pair of eyes might
help.

The following code is giving me a runtime "3075 - Syntax Error (missing
operator) in expression 'WHERE [CampaignID]=3 AND [Export]=-1' "

Dim varFilePath
varFilePath = DLookup("[FilePath]", "Campaigns_Documents", "WHERE
[CampaignID]=" & Forms!Campaigns!CampaignID & " AND [Export]=-1")

- Table Campaigns_Documents is a linked table. I can open it (to verify
connection) and it is spelled correctly
- FilePath is a Text field
- CampaignID is a Long Integer number field
- Export is a Yes/No field
- Based on the error message I can see that the Forms!Campaigns!CampaignID
is evaulation properly.
- If I create a query and use the WHERE statement as shown in the error it
runs fine and returns a single record.
- I have decompiled/compiled (nor errors), repaired and compacted
- I rubbed my tummy and patted my head

I am going crazy here wondering what I am doing wrong. I am guessing it is
staring me in the face and I am just overloooing the obvious. Any
suggestions here?

Tony Vrolyk

 

[fredg]

OK, I have done DLookups a lot but something is escaping me right now. I
cannot figure out the problem here so I thought a fresh pair of eyes might
help.

The following code is giving me a runtime "3075 - Syntax Error (missing
operator) in expression 'WHERE [CampaignID]=3 AND [Export]=-1' "

Dim varFilePath
varFilePath = DLookup("[FilePath]", "Campaigns_Documents", "WHERE
[CampaignID]=" & Forms!Campaigns!CampaignID & " AND [Export]=-1")

- Table Campaigns_Documents is a linked table. I can open it (to verify
connection) and it is spelled correctly
- FilePath is a Text field
- CampaignID is a Long Integer number field
- Export is a Yes/No field
- Based on the error message I can see that the Forms!Campaigns!CampaignID
is evaulation properly.
- If I create a query and use the WHERE statement as shown in the error it
runs fine and returns a single record.
- I have decompiled/compiled (nor errors), repaired and compacted
- I rubbed my tummy and patted my head

I am going crazy here wondering what I am doing wrong. I am guessing it is
staring me in the face and I am just overloooing the obvious. Any
suggestions here?

Tony Vrolyk

Read Access help on DLookUp:
.... criteria is often equivalent to the WHERE clause in an SQL
expression, without the word WHERE ...

The Where clause in a DLookUp does NOT contain the literal word Where.

varFilePath = DLookup("[FilePath]", "Campaigns_Documents",
"[CampaignID]=" & Forms!Campaigns!CampaignID & " AND [Export]=-1")

 

[Tony Vrolyk]

Holy crap I knew that! So the title of my post was accurate. Thanks for the
kick in the pants.

Tony

OK, I have done DLookups a lot but something is escaping me right now. I
cannot figure out the problem here so I thought a fresh pair of eyes
might
help.

The following code is giving me a runtime "3075 - Syntax Error (missing
operator) in expression 'WHERE [CampaignID]=3 AND [Export]=-1' "

Dim varFilePath
varFilePath = DLookup("[FilePath]", "Campaigns_Documents", "WHERE
[CampaignID]=" & Forms!Campaigns!CampaignID & " AND [Export]=-1")

- Table Campaigns_Documents is a linked table. I can open it (to verify
connection) and it is spelled correctly
- FilePath is a Text field
- CampaignID is a Long Integer number field
- Export is a Yes/No field
- Based on the error message I can see that the
Forms!Campaigns!CampaignID
is evaulation properly.
- If I create a query and use the WHERE statement as shown in the error
it
runs fine and returns a single record.
- I have decompiled/compiled (nor errors), repaired and compacted
- I rubbed my tummy and patted my head

I am going crazy here wondering what I am doing wrong. I am guessing it
is
staring me in the face and I am just overloooing the obvious. Any
suggestions here?

Tony Vrolyk

Read Access help on DLookUp:
... criteria is often equivalent to the WHERE clause in an SQL
expression, without the word WHERE ...

The Where clause in a DLookUp does NOT contain the literal word Where.

varFilePath = DLookup("[FilePath]", "Campaigns_Documents",
"[CampaignID]=" & Forms!Campaigns!CampaignID & " AND [Export]=-1")