Link to home
Start Free TrialLog in
Avatar of EnvAgency
EnvAgency

asked on

IIF in Access Query randomly forgets label name and creates SQL error....weird!

I have a subform that contains a date and time in a control.  I'm using a query to check whether a date and time in a table is greater then the subform time. The date and time must remain seperate entities (the now function will not work for what I need to do).  In the query the Date from the table (Date Changed) has the criteria...
>=IIf(IsNull([Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![DateOfSynch]),[DateChanged],[Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![DateOfSynch])

Obviously if the form control is null it will accept all dates from the table [DateChanged] otherwise the criteria in based on the subform..... >=Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![DateOfSynch])

This part seems to work okay.

The other criteria (time) is acting really odd.
I have defined the field in the query as:
IIf([datechanged]<>[Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![DateOfSynch],#23:59:59#,[TimeChanged])
This makes sure that if the date is not the same as the form control date then the time is set to 23:59:59 (as late as possible).
The criteria is set as:>=IIf(IsNull([Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![TimeOfSynch]),[TimeChanged],[Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![TimeOfSynch])

Now the weird thing is, is that you can't just write an iif statement in the field, you should give it a label
e.g Time1:IIf([datechanged]<>[Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form] ............

Once I have run the query and gone back into the QBE grid, the label has dissapeared and just reads
IIf([datechanged]<>[Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form] ............

More strange is that this still seems to work, only when I save the query and then try to go back into the design I get the error message Syntax Error (missing Operator) in query expression........
Maybe I need to rethink how I'm designing the query?

Any help much appreciated, I hope I haven't dragged this out in too much detail.

Darren


INSERT INTO tbl_Synch_TrackChanges_Both ( NewRecordID, MaxOfDateChanged, MaxOfTimeChanged, TableChanged, LastOfTypeOfChange, [Database] )
SELECT lnkTrackChanges1.NewRecordID, Max(lnkTrackChanges1.DateChanged) AS MaxOfDateChanged, Max(lnkTrackChanges1.TimeChanged) AS MaxOfTimeChanged, lnkTrackChanges1.TableChanged, Last(lnkTrackChanges1.TypeOfChange) AS LastOfTypeOfChange, "Linked" AS [Database]
FROM lnkTrackChanges1
WHERE (((lnkTrackChanges1.DateChanged)>=IIf(IsNull([Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![DateOfSynch]),[DateChanged],[Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![DateOfSynch])) AND ((IIf([datechanged]<>[Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![DateOfSynch],#12/30/1899 23:59:59#,[TimeChanged]))>=IIf(IsNull([Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![TimeOfSynch]),[TimeChanged],[Forms]![frm_SynchMainForm]![frm_SynchDetails].[Form]![TimeOfSynch])))
GROUP BY lnkTrackChanges1.NewRecordID, lnkTrackChanges1.TableChanged, "Linked";

Open in new window

Avatar of Natchiket
Natchiket
Flag of United Kingdom of Great Britain and Northern Ireland image

Avatar of EnvAgency
EnvAgency

ASKER

Thanks Natchiket.
I already knew how to bring the SQL into VB. This still hasn't resolved the problem though.
I either need a new method of writing the query or at least a reason why 1 minute the SQL is written correctly but then when saved it changes the SQL (rewrites the bloody thing) to something else that in fact causes an error. Access really is a pain!
ASKER CERTIFIED SOLUTION
Avatar of Natchiket
Natchiket
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I will shut this down as the person who asked this question is a muppet and has gone on holiday.

Cheers,

James