DateAdd - number causing trouble

[LetsHope]

Hi there,



I hope someone will be able to help me with this problem I have been trying to solve for a few hours now...
I have the following in a query (sorry I work in French):

Prevision: VraiFaux([Tb_Inspections]![Inspection]="E";AjDate("aaaa";[FreqExterne];[DateInspection]);VraiFaux([Tb_Inspections]![Inspection]="I";AjDate("aaaa";[FreqInterne];[DateInspection]);VraiFaux([Tb_Inspections]![Inspection]="U";AjDate("aaaa";[FreqUltrasons];[DateInspection]))))

I have no problem running the query. The problem is only if I put a criteria in this field (e.g.

>#01-04-16#

or

>=VraiFaux(EstNull([Formulaires]![Fm_Prevision]![TxtPrevDu]);#01-01-1900#;[Formulaires]![Fm_Prevision]![TxtPrevDu]) Et <=VraiFaux(EstNull([Formulaires]![Fm_Prevision]![TxtPrevAu]);#31-01-2100#;[Formulaires]![Fm_Prevision]![TxtPrevAu])

Moreover, when I try to link my Query to an Excel sheet, I also get an error message.

BUT...

If I change [FreqXXXX] for 2, or 3 or any number, everything works fine, even the link in Excel.

Note that my Frequence fields are numbers (long integer) with no decimals, and that they range from 1 to 3.

What is the problem with my [Frequence] fields?

Thank you a lot in advance for your help, I am turning zombie...

LetsHope

[rpeare]

if you are using m/d/y format (the access default which I don't recall if you can change or not) try using cdate() on your prevision calculation to make sure the calculation is being treated as a date.

[CJ_London]

you haven't actually said what your problem is - you get an error message, wrong results returned, something else?

but if this

>#01-04-16#

is 1st April 2016 then you need to use

>#04-01-16#

otherwise it is interpreted as 4th January 2016

as rpeare says - it needs to be mm/dd/yyyy format, not sure about using a '-' rather than '/' it may be down to windows language settings.

[LetsHope]

Hello rpeare and Ajax,

First thank you so much to spend some time on my problem.

you haven't actually said what your problem is - you get an error message, wrong results returned, something else?

The message I get when I put a criteria in my Prevision field is "Data type mismatch in criteria expression".

As for the date format, everything is fine in all the other queries of the same database and their criterion (remember I am working on a French Canadian computer with French version of Access).

What is really strange is that, if I replace [FreqExterne], [FreqInterne] and [FreqUltrasons] in the expression with the numbers that they represent, all is good:

Prevision: VraiFaux([Tb_Inspections]![Inspection]="E";AjDate("aaaa";2;[DateInspection]);VraiFaux([Tb_Inspections]![Inspection]="I";AjDate("aaaa";1;[DateInspection]);VraiFaux([Tb_Inspections]![Inspection]="U";AjDate("aaaa";3;[DateInspection]))))

This is so mysterious to me.

Thanks again!

[CJ_London]

the implication of the error message is that AjDate("aaaa";2;[DateInspection]) is a different datatype to >#04-01-16# so I suggest you do as rpeare suggested

Prevision: cdate(VraiFaux([Tb_Inspections]![Inspection.....)

criteria cdate("04-01-16")

I suspect cdate needs to be changed to the French equivalent.

[LetsHope]

Good evening Ajax,

I tried... but still the same message.

Cul de sac?

Desperately yours...

[Micron]

Guys, I think this is what's going on (pardon any lack of proper French punctuatio):
Stating the obvious, he/she is using the DateAdd function - DateAdd (interval, number, date). "a" is anne (annay), so 'aaaa' is yyyy
The number expected is [FreqExterne] and the date is [DateInspection]. I would agree that "Data type mismatch in criteria expression" is because text or null is being passed to the function. That's why it works when the number 2 was put in. Or am I all wet??

@LetsHope: you need to figure out what's being passed to the expression. It cannot be Null or text. Post back if you need help on how that can be determined, but you'll have to provide info about what's running this sql statement or query that you have.

[LetsHope]

Hello Micron,

You've got it: "aaaa"="yyyy", AjDate = DateAdd, [FreqExterne] or [FreqInterne] or [FreqUltrasons] are the number, and finally [DateInspection] is the date.
Here is the expression picked from the SQL window:

IIf([Tb_Inspections]![Inspection]="E",DateAdd("yyyy",[FreqExterne],[DateInspection]),IIf([Tb_Inspections]![Inspection]="I",DateAdd("yyyy",[FreqInterne],[DateInspection]),IIf([Tb_Inspections]![Inspection]="U",DateAdd("yyyy",[FreqUltrasons],[DateInspection]))))))>#4/1/2016#))

I checked in the table and the three fields are numbers, long integer, but I have one record ("Other") that has 0 in each field ([FreqExterne] or [FreqInterne] or [FreqUltrasons]). I replaced 0 with 100, but I still get the same error.

I have no empty field otherwise.

Let's cry....

[Micron]

If there was only one record in the table being referenced by the function, it might work. You haven't set any criteria on the table in that function (as far as I can see), so Access doesn't know which record to pick. The table data may be numbers, but the value being passed to the function may be Null. Don't forget - it worked with number 2. It has to be wrong data type (or no data, which can be the same thing). I don't think you can use this extremely convoluted expression in a query if you expect it to get these values from itself. That many nested IIF's can give me a headache! I'd look for a different approach if you can't pass criteria to the dateadd function.

[rpeare]

How about you provide a sample of your database (the two tables and the query you're having trouble with) with some junk data in each table. It should not matter if you are adding an integer to a date with the dateadd function, and even if it did, it should be handled by the cdate function so I have no idea why you are having this much trouble with it.