Solved DLookup Not Changing Value

[DDJKSA]

Hi Guys

Been around around in circles with this one, and probably (hopefully) a trivial one to solve. I have a form which generates a single number (Text41 below) from 1 to 12 (depending on Text6 on the form). The numbers generated are all correct (see 'Immediate' window below). I'm then trying to use Dlookup to convert the number to a month name (Jan-Dec) by referencing a table (tblMonths) which has 2 columns (Num and Mth), one of numbers from 1 to 12 and the other with matching month names from Jan to Dec. The criteria string (strmth below) is correct (see ''Immediate' window below) but the value returned is always "Jan" (the first record in tblMonths). If I replace the variable strmth in DLookup with the actual value (eg. "Num=8" or "Num='8'"), the month returned is then correct (August in this case). The reason for 2 different criteria strings is because I have formatted Num both as a string and as a number to see if it made any difference....it didn't! Is it something really obvious and daft I'm doing (or not doing)? Am I triggering it on the wrong event (onCurrent)?

Thanks for any advice

DDJ

 

[Minty]

Your string shouldn't have any quotes around it, they are included by default in the variable.
It should be Num = 4.
So your DLookup should be

DLookup("Mth","tblMonths","Num = 4")

The double quotes will cause a issue.

You can type that expression directly into the Immediate window to prove a point.

 

[DDJKSA]

Thanks Minty

I mistakenly thought the criteria had to be within quotes too. Sorted

DDJ

Your string shouldn't have any quotes around it, they are included by default in the variable.
It should be Num = 4.
So your DLookup should be

DLookup("Mth","tblMonths","Num = 4")

The double quotes will cause a issue.

You can type that expression directly into the Immediate window to prove a point.

 

[Gasman]

Thanks Minty

I mistakenly thought the criteria had to be within quotes too. Sorted

DDJ

It would be if you wanted "Num = 'DDJKSA'"

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.



Exaample:

tt="Eg'g"

? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

 

[RogerCooper]

You don't need a lookup. You can use the MonthName function, combined with the Left function to choose the first 3 characters.

 

[MajP]

Text41 = format([text6],"mmm")

I would simply use Format not dlookup or monthname.

 

[MajP]

@DDJKSA,
Do yourself a favor and any control that you are going to use in code give it a good name. If you never use it in code, then it does not matter as much. But when you have to come back and debug or the project starts getting big, you will save a lot of frustration. Especially when you have other people look at it.

TxtMonthName = format([txtMonthNumber],"mmm")

 

[DDJKSA]

It would be if you wanted "Num = 'DDJKSA'"

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.



Exaample:

tt="Eg'g"

? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

Cheers Gasman

I did have the criteria in a string variable in the code to debug but it's all sorted now - just my misunderstanding.

Thanks for your help.

DDJ

 

[DDJKSA]

Text41 = format([text6],"mmm")

I would simply use Format not dlookup or monthname.

Thanks @MajP

I got my Dlookup working but wanted to try your solution as it's much neater.

Text41 = Format(Right([Text6], 1), "mmmm")

Unfortunately it still returns January for each number (the numbers calculate correctly in a debug windows)

Weird?

DDJ

 

[cheekybuddha]

Unfortunately it still returns January for each number (the numbers calculate correctly in a debug windows)

Compare what @MajP posted with what you used. He used the Format() function on the whole of Text6, not just a single character extracted.

Do you have Option Explicit declared at the top of all your code modules (above or below Option Compare Database)? If not, you should!

This will highlight errors with undeclared and mis-spelt variables in your code, which will save you a lot of headaches down the line.

See here for how to enable it automatically for all newly created modules

 

[Gasman]

I would use MonthName?

 

[Minty]

Just to expand on Gasman's answer from the above;

i = 1 as a date = 31/Dec/1899
i = 2 as a date = 01/Jan/1900
i = 3 as a date = 02/Jan/1900

See why it doesn't work?

And why this does:

 

[DDJKSA]

Just to expand on Gasman's answer from the above;

i = 1 as a date = 31/Dec/1899
i = 2 as a date = 01/Jan/1900
i = 3 as a date = 02/Jan/1900

See why it doesn't work?

And why this does:
View attachment 117285

Thanks @Minty

Yes, I totally see why it doesn't work with your simple explanation - just me being daft and thinking it through as usual! Appreciate your help.

Cheers

DDJ

 

[DDJKSA]

Thanks @cheekybuddha

Everytime I try and reply to your post I get the following message....hence this reply route!

Only the last digit in the variable Text6 is used to determine the month number, hence the extraction but I do get your point about the whle string needing to be a date format. Thanks for your suggestion on using the OE statements which I'll certainly do from now on.

Regards

DDJ

 

[DDJKSA]

Thanks @Gasman..that looks like a nice neat solution and I'll try that.

Appreciate your help and input.

Regards

DDJ

I would use MonthName?
View attachment 117283

View attachment 117284