Data Type Mismatch

[aztrix]

ok im having problems with the data type mismatch error. this is giving me some grief but simply i am comparing the two fields ExpiryDate (Date)> CurrentDate (Now()) in a query expression to return a '0' or '-1' if the ExpiryDate is larger than the CurrentDate. this works fine.

however when i attempt to filter this in the next query to only return '-1' values i get a data type mismatch.

any suggestions?

 

[boblarson]

What is the exact syntax you used for creating this field you want to use with criteria? It sounds like you set the values to be string if you enclosed them in quotes. They should just be put in as numbers.

So something like:

MyField:IIf([ExpiryDate]>Date(),-1,0)

 

[aztrix]

ExpiryDate: DateAdd("m",(qryPersonnelAttachedRules_1!Validforperiod),tblRuleAttached!DateCompleted)

 

[aztrix]

then....

RuleExpired: [ExpiryDate]<Now()

 

[boblarson]

Is DateCompleted a date field or does it include TIME? If it is only date, do not use NOW as that includes a time component. Use DATE() instead.

Also, this should not be like this:
RuleExpired: [ExpiryDate]<Now()

if you want to use the -1 or 0

It would be:

RuleExpired: IIf([ExpiryDate]<Date(),-1,0)

 

[aztrix]

ok, the date fields are just Date not time. ok thanks for your help, ill give it a shot.

 

[aztrix]

is your way (the correct way) returning a string or integer?

 

[boblarson]

is your way (the correct way) returning a string or integer?

My way SHOULD be returning a bit value of either -1 for true or 0 for false. (it could also be classified as Integer).

 

[aztrix]

ok so how do i query this using it as criteria, as i still get the data type mismatch when i place -1 in the query criteria.

 

[boblarson]

ok so how do i query this using it as criteria, as i still get the data type mismatch when i place -1 in the query criteria.

Once you have the item in place, you will need to save that query and then use it in another query in order for the criteria to work. Don't ask me why, it just happens that way sometimes.

 

[aztrix]

yup done that, the maths are done in query1 then i try to criteria the Query1 RuleExpired field in Query2 but i still get the error sorry bob

 

[boblarson]

yup done that, the maths are done in query1 then i try to criteria the Query1 RuleExpired field in Query2 but i still get the error sorry bob

Can you post the database? (at least the tables that make up Query1 and Query2, and include Query1 and Query2)?

 

[aztrix]

ok maybe you can do a check on all the queries and let me know where im going wrong anyways that would be fantastic mate.

 

[aztrix]

View attachment Data -.zip


ok the queries you want to look at are:
qryPersonnelAttachedRules_2
qryPersonnelAttachedRules_3
qryPersonnelAttachedRules_AllCals or AllAlerts

you should also check out the daisy chain of queries to get to these ones above if you have time, its quite amusing and could possibly hold the key.

basically this daisy chain of queries was created to find the the AttachedRuleRecordID of each maxOfDateCompleted for each rule a host(Personnel) completed. you may have to see my other thread i posted yesturday on here to get a better flow for it.

you help is greatly appreciated bob.

 

[aztrix]

im wondering if you are looking at my database and just laughing in disgust ha ha