Dlookup problem (1 Viewer)

NT100 · Jan 20, 2019

I built the following code with DLookup but runs into Run-time 2465 Microsoft Access can't find the field '|1' referred to in your expression.

vTPCode = DLookup("[TPCode]", "tblTWTTP", "[TRef]=" & iID & " AND [TPCode]=" & iTPCode & " AND Date() BETWEEN #" & [ApptStart] & "# AND #" & [ApptEnd] & "#")

Note: tbllTWTTP has fields of TPCode, ApptStart, ApptEnd.

I would be appreciated if you could suggest.

Thank you.

Gasman · Jan 20, 2019

What about TRef ?

Create a strWHERE string and make the where parameters into the string, then debug.print the string.?

NT100 · Jan 20, 2019

Gasman said:
What about TRef ?

Create a strWHERE string and make the where parameters into the string, then debug.print the string.?

Sorry that TRef is also a field in tblTWTTP.

sCritera = "Date() BETWEEN #[" & ApptStart & "#] AND #[" & ApptEnd & "#]"

It causes compile error of variable not defined of "ApptStart".

How would you do it?

Solo712 · Jan 20, 2019

NT100 said:
I built the following code with DLookup but runs into Run-time 2465 Microsoft Access can't find the field '|1' referred to in your expression.

vTPCode = DLookup("[TPCode]", "tblTWTTP", "[TRef]=" & iID & " AND [TPCode]=" & iTPCode & " AND Date() BETWEEN #" & [ApptStart] & "# AND #" & [ApptEnd] & "#")

Note: tbllTWTTP has fields of TPCode, ApptStart, ApptEnd.

I would be appreciated if you could suggest.

Thank you.

Probably, the red is your problem. It's not a field name...

Best,
Jiri

NT100 · Jan 20, 2019

Solo712 said:
Probably, the red is your problem. It's not a field name...

Best,
Jiri

I used a date type variable as follows

dToday = Format(Date, "Medium Date")

vTPCode = DLookup("[TPCode]", "tblTWTTP", "[TRef]=" & iID & " AND [TPCode]=" & iTPCode & " AND " & dToday & " BETWEEN #" & [ApptStart] & "# AND #" & [ApptEnd] & "#")

However, I still get the same error.

Gasman · Jan 20, 2019

Firstly as pointed out, the way you are expressing it is

DateFieldInTable BETWEEN

and you have no DateFieldInTable?

I was just considering putting the whole criteria into a string then debug.print that string to see if it contains what you *think* it contains?

I believe you want " AND [ApptStart] >= Date() AND [ApptEnd] <= Date()"

isladogs · Jan 20, 2019

This really doesn't make any sense.

First of all you are looking up the value of TPCode then using TPCode as one of the conditions ...so the answer is iTPCode!

And the date part is wrong (as Gasman has just said)

So it should be (assuming TPCode is a number field):

vTPCode = DLookup("[TPCode]", "tblTWTTP", "[TRef]=" & iID & " AND [TPCode]=" & iTPCode & " AND [ApptStart] <= #" & Date & "# AND [ApptEnd]>= #" & Date & "#")

simplifying to
vTPCode = DLookup("[TPCode]", "tblTWTTP", "[TRef]=" & iID & " AND [ApptStart] <= #" & Date & "# AND [ApptEnd]>= #" & Date & "#")

or

vTPCode = iTPCode

Gasman · Jan 20, 2019

Well spotted Colin, I was not looking at the logic, just the syntax.:banghead:

I did test my syntax with a query before posting though.

isladogs · Jan 20, 2019

Your suggestions were indeed logical as well as syntactically correct.

It would only as I was about to post my answer that I noticed the TPCode field in the condition part on top of everything else

NT100 · Jan 20, 2019

How come you didn't use 'between' in the date filter?

isladogs · Jan 20, 2019

You have two date fields in use here both being compared in different ways with the current date. So I can't see any way you can use BETWEEN ... AND

I would certainly use BETWEEN if your condition included one or both date fields being between 2 date values. For example:
[DateStart] Between #" & Date "# AND #" & Date+7 & "#"

Anyway am I correct that it all just amounts to: vTPCode = iTPCode

NT100 · Jan 21, 2019

isladogs said:
You have two date fields in use here both being compared in different ways with the current date. So I can't see any way you can use BETWEEN ... AND

I would certainly use BETWEEN if your condition included one or both date fields being between 2 date values. For example:
[DateStart] Between #" & Date "# AND #" & Date+7 & "#"

Anyway am I correct that it all just amounts to: vTPCode = iTPCode

Yes, it works fine after the omission of "[TPCode]=" & iTPCode" in the query. I overtook my query.

For the date comparison, it seems that I only focused on the syntax without looking into the detail limitations of "Between" usage.

Thank you very much.

Dlookup problem (1 Viewer)

NT100

Registered User.

Gasman

Enthusiastic Amateur

NT100

Registered User.

Solo712

Registered User.

NT100

Registered User.

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

NT100

Registered User.

isladogs

MVP / VIP

NT100

Registered User.

Similar threads

Users who are viewing this thread