DLookup with three criteria

[Lkwdmntr]

Can someone tell me what's wrong with this? These are so confusing. I never know when to put the quotes and when not to.

DLookup("[WeeklyPlanID]", "tblWeeklyPlans", "[UserID] = " & Me.txtUserID & " AND [Loop] = '" & loopNum & "' AND [WeekNumber] = '" & weekNum & "'")

I'm getting a data type mismatch in criteria expression error 3464

The UserID field is a Long in the DB and the other two are Integers.

 

[theDBguy]

What data types are loopnum and weeknum?

 

[Lkwdmntr]

What data types are loopnum and weeknum?

I use a Dim statement and they are set to Integer

 

[Lkwdmntr]

I got it. Just played around with it some more. Ended up with this.

DLookup("[WeeklyPlanID]", "tblWeeklyPlans", "[UserID] = " & Me.txtUserID & " AND [Loop] = " & loopNum & " AND [WeekNumber] = " & weekNum)

Thanks anyway!

 

[theDBguy]

I got it. Just played around with it some more. Ended up with this.

DLookup("[WeeklyPlanID]", "tblWeeklyPlans", "[UserID] = " & Me.txtUserID & " AND [Loop] = " & loopNum & " AND [WeekNumber] = " & weekNum)

Thanks anyway!

Congratulations! Numeric data types don't need quote delimiters. Good luck with your project.

 

[Gasman]

Do yourself a favour.
Put all the criteria into a string variable and use that.

That also enables you to Debug.Print strCriteria so you can see if the syntax is correct.
I generally use single quotes for text unless the text also has a single quote in it, then I *think* it is triple double quotes.
Dates need to be surrounded with # and numerics are not surrounded with anything.

 

[Lkwdmntr]

I tried that and still didn't work. Getting a syntax error (missing operator) in query expression '[UserIDFK] = AND [StatusFK] <> '

DCount("[WeeklyPlanID]", "tblWeeklyPlans", "[UserIDFK] = " & lngUserID & " AND [StatusFK] <> " & status)

 

[theDBguy]

I tried that and still didn't work. Getting a syntax error (missing operator) in query expression '[UserIDFK] = AND [StatusFK] <> '

DCount("[WeeklyPlanID]", "tblWeeklyPlans", "[UserIDFK] = " & lngUserID & " AND [StatusFK] <> " & status)

In that case, you could try.

DCount("*", "tblWeeklyPlans", "[UserIDFK] = " & Nz(lngUserID,0) & " AND [StatusFK] <> " & Nz(status,0))

 

[Gasman]

Please do Not post the same question multiple times.

 

[Lkwdmntr]

In that case, you could try.

DCount("*", "tblWeeklyPlans", "[UserIDFK] = " & Nz(lngUserID,0) & " AND [StatusFK] <> " & Nz(status,0))

Same error. Not sure what's going on with this one.

 

[theDBguy]

Same error. Not sure what's going on with this one.

It might be easier if you could post a sample copy of your db with test data.

 

[Lkwdmntr]

It might be easier if you could post a sample copy of your db with test data.

I was hoping to avoid that, but I'll try to take out what's not necessary and see if I can get it small enough. What is the max size limit for sending files?

 

[arnelgp]

you don't need to post any db.
just post a Snapshot of the Table Structure of tblWeekPlans (especially those fields involved in DCount()).

 

[Lkwdmntr]

There you go.

 

[theDBguy]

What is the max size limit for sending files?

Just FYI, the size limit is 7MB.

 

[arnelgp]

statusFK is String:

DCount("*", "tblWeeklyPlans", "[UserIDFK] = " & Nz(lngUserID,0) & " AND [StatusFK] <> '" & Nz([status],"@!") & "'")

 

[Lkwdmntr]

Just FYI, the size limit is 7MB.

I just tried and it fits without any deleting. It's not nearly as big as the first version and it does a lot more. I took all the advise on here and redesigned it. I have another question out there about filtering subreports. I will upload the db on that one. Thanks for everything.

 

[theDBguy]

I just tried and it fits without any deleting. It's not nearly as big as the first version and it does a lot more. I took all the advise on here and redesigned it. I have another question out there about filtering subreports. I will upload the db on that one. Thanks for everything.

I suggest you zip it up before posting. Cheers!

 

[Lkwdmntr]

statusFK is String:

DCount("*", "tblWeeklyPlans", "[UserIDFK] = " & Nz(lngUserID,0) & " AND [StatusFK] <> '" & Nz([status],"@!") & "'")

didn't work either. I just realized that "lngUserID is used as a parameter in a function. I should be just glngUserID, I tried it with that and it still didnt' work. Taking a break for tonight. I'll be back at it tomorrow. Thanks!

 

[Lkwdmntr]

I suggest you zip it up before posting. Cheers!

Not tonight though. Off to bed for me. Thanks again for everything.