Expression Error

[Perfac]

I am aging or I got some virus. My system has a few of these expressions and they work. I miss the issue here. Error on the one below. "The expression you entered has an invalid date value?
Salary069d: Dlookup("SalOrWageRate071","q03SalNWgeReviews","Em ploy_ID071 ="& Employ_ID069d And #" & Format([LstDayOfMnth069d],"yyyy/mm/dd") & "# Between [StartDate071] And [Enddate071b]")

Sometimes a space appears. When I click on edit here the space disappears. No space in Employ.

[Micron]

The space is input by the forum after 50 contiguous characters. You need to use code tags to get around that.
You need to research how DLookup works and how to write them.

DLookup can only return one value. When applying criteria, you're saying WHERE somefield contains somevalue. You can't say where somevalue lies between 2 values. If you do and it even works, it will return the first value it finds that satisfies both criteria. In your case, the logic being presented is that someDate lies somewhere within a set of records that satisfy other criteria specified.

So it may turn out that the problem is related to how dates are stored in your fields vs what you're asking for, and that has to do with your Regional Settings for dates. Regardless of how you format a date field, the value is stored as a decimal that corresponds to mm/dd/yyyy. When you start formatting dates in expressions using the Format function (which by the way, coerces a value to a string) you can run in to trouble. I say that because even if you sort that out here, I don't see the lookup working for the aforementioned reasons.

[Perfac]

I was a very active sportsman and business man up to 50 y.o. Became disabled (wheel chaired) for two years, healthy again. Decided to learn programming in that time. After 4 years of self training I still haven't done enough to exactly understand all about the expressions and the "syntaxes'. Through time I built a referral list. I will do more but if the virus doesn't interfere I have a demanding business project coming up, which will do well with the system Ruben and me have built so far. It is a passion, obsession, interest, sensation and a hobby to improve our system to the most I would like it to be.

The regional settings on my computers for dates, is mainly dd mmm yyyy. As I said we have a couple of those exact expressions in our system and it works well. I copied it to use it again here and replaced the field names. Whatever way of course the end result that is needed, is needed. I trust Access always have a way for anything needed. In this case there is only one value that will satisfy the criteria. Learning from you, I accept the problem here lies in the format of the 3 dates fields involved, and how they are stored. Our part of the world only works with the format of dates of my regional setting, I would have to keep it that way. I will play around with the ways those three dates are stored.

[Micron]

Your collaboration sounds like how I learned a lot. The rest from creating (could not have been as successful without Google) and what you learn by trying to help others. That part keeps it fresh, because for me, it's not like riding a bike (at least I think I still could).

I will play around with the ways those three dates are stored.

You can't. Bill Gates decided on the US format and that's it. You can make them look different but the date is a number - 8 byte double precision floating point - which represents the number of days since (don't quote me - December 31 1899). That may have changed because I've read recently that the span is 100 AD to 9999. Not sure of the significance of that. However, I do advise to always use 4 digit year because assumptions are made by the application. You might think that 20 is OK, but is that 1920 or 2020? It's not wise to leave the decision to the application IMO.

As for it being a number, try this in your immediate window to see what you get with your regional settings:
?cdbl(#04/11/2020#) << one line at a time and hit return at the end. Don't forget the question mark.

?cdbl(#11/04/2020#)
I get 43932 and 44139. Try it with 04/25/2020 (US format for today) and 25/04/2020

I never have an issue with dates because of my locale, so I'm no expert on how to handle those that don't conform to the system. Knowing how they're stored should put you on the right path at least. Last bit of wisdom I can impart about dates is that if you ever need to do date comparisons for work periods that span over midnight, I'd store Now() in that field, not Date and not Date in one field and Time in another. You can format that field as a date and hide the time, but without the time component you will have trouble with the calculation.

[Perfac]

Apologies for not understanding exactly. I have tried a few things, as I said there are a couple of those exact expressions in my system working. I checked if the format where the three dates are stored are the same than the ones working, I can not see why they work and this one not. Attached is part of my system. I want to add the expression in thread one here to query q04SalaryProcessing05. Please see if you can find the issue.

[Micron]

If you enter

Salary069d: DLookUp("SalOrWageRate071","q03SalNWgeReviews","Employ_ID071 =" & [Employ_ID069d] And " & Format([LstDayOfMnth069d],yyyy/mm/dd) & " Between [StartDate071] And [Enddate071b])

it will run, but it will prompt you for the last 2 dates because they are neither in the query nor are they a form field reference, so Access doesn't know what they are. I entered 2 dates and it ran, but it returned #Error in the field.

What you have there looks like one of the most convoluted db structures I've ever seen. If you have to modify anything in about a year from now, you will likely find it difficult to follow even though you built it. So getting you this far is about all I can do with this current problem. I see that you have even formatted number fields to look like text values - very confusing and not advised.

[Perfac]

I added a post here, don't know why it is gone? The expression below does the job. In post #1 there was a spelling error "SalOrWageRate071", and a couple of concentration errors on my side, syntaxes left out, not dating formatting issues. Saying my structure is convoluted, I received as a compliment. I set up a query that lists all live query names. I linked it with a table where we have fields of the long text type. My son Ruben and me have an agreement to describe the use and setup reason of every query. I agree with you, if we don't have that, we will have issues in a years time. Our system has 900 queries now, too much time to write an essay on the "easy to understand" queries, we have to have the skill to know which queries must be well documented, and which ones not. This form is not accessible to users.

Salary069e: DLookUp("SalOrWgeRate071","t03SalNWgeReview","Empl oy_ID071 =" & [Employ_ID069e] & " And #" & Format([LstDayOfMnth069e],"yyyy/mm/dd") & "# Between [StartDate071] And [Enddate071a]")