Query to show those with a date before a calculated date field

[Pippa1]

Hi,

I have a field called Randomised Date and in a query I have created a field called 2 year follow-up date. In the Field in the query it says:

2 Year follow-up date: DateAdd(2yyyy, 2[Randomised Date]).

In the Criteria for this I have written <=[Enter the last due follow-up you'd like to see].

My intention was that when the query is run I can see only those who have a 2 year date before or equal to a certain date but it isn't working.

It brings up the parameter question and I enter a date but it shows me people who's date is later than that.

Any ideas what I could have done wrong?

It is doing the adding correctly just not the selection.

 

[plog]

When users submit data it gets treated as text and compared as such. That means---

As text 12/12/2017 comes before 6/6/2017 because the first character is 1 compared to 6.

It's not smart enough to know the data it is looking at are dates--so you need to explicitly tell it that everything is a date. That means wrapping the fields in CDates (https://www.techonthenet.com/access/functions/datatype/cdate.php)

 

[Pippa1]

Thank you.

So even though I have that field set as a date/time data type in the table and it is understanding it is a date enough to add 2 years to it I have to do something to it for the parameter query to work?

 

[plog]

I don't believe so but it couldn't hurt. What you definitely need to do is wrap the user input data in CDate.

 

[Pippa1]

The data gets uploaded from an excel file rather than a user inputting it.

 

[plog]

Not according to your initial post:

In the Criteria for this I have written <=[Enter the last due follow-up you'd like to see].

 

[Pippa1]

Sorry. So the data in the table gets uploaded from excel but then when people use the query they enter the date that they are interested in.

It's that data entry that I need to "wrap"?

 

[plog]

Yes it is.

 

[Pippa1]

Do I have to use code for this or can I add CDate into the criteria statement I copied above? I've tried a few places and it doesn't seem any better. Now I think I've understood the theory of why it's not working (thank you) it would seem to make sense to me if it was
<=CDATE([Enter the last due follow-up you'd like to see]) but that doesn't seem to be working.

 

[plog]

That should do it. Could you post a sample of your database? Also, give me an example of what you are inputting and what you expect.

 

[Pippa1]

It seems to have started working. Hooray, thank you.

Now just to be cheeky, I'd really like it to be set so that people had to enter a date in short date form and that there was an input mask to make them do this.

I made the format short date and added the relevant input mask in the property sheet for that field of the query but it isn't working.

I'm sorry but it's difficult for me to show you the database because if contains sensitive personal information so is saved in a secure location.