IIf query: syntax error (comma) in query expression

[neilduffy1024]

Hi all,

I a new to Access but have some experience with VBA and am reasonably comfortable with coding.

I'm trying to run a very basic iif statement to correct hourly data for sorting. Basically, a trading day runs from 8am - 8am, so I need to adjust the hours to ensure that 1am on the 15th trading day (really the 16th on the calendar), comes after 9am on the 15th trading day (which will actually be the 15th on the calendar).

Here's what I've used. It's driving me bananas, because it keeps telling me that there's a syntax error (comma) in the query expression, but I can't understand why?

Sort2: IIf([DELIVERY_HOUR]<8,[DELIVERY_HOUR]+24,[DELIVERY_HOUR])

Any help would be much appreciated.

Cheers,

Neil

P.S. I know there are similar threads on IIf statements, and having looked at them I can't see why the syntax would be wrong, so I'm wondering if I'm missing something else.

 

[JHB]

Depending of from where you're in the world, you've to use ; instead of ,.

 

[neilduffy1024]

Hi JHB,

Thanks for the reply.

I'm in Australia, where the syntax is normally , rather than ;

That said, I thought it might have been that and tried it anyway. Unfortunately, it then flagged a syntax error before I could enter enter the expression.

I should note than the syntax error message I receive (using the comma) does not occur when entering the expression, but only when I try to run the query.

Cheers,

Neil

 

[Uncle Gizmo]

It looked OK to me so I thought I would wait before responding.

When you get a bug in your code like this its not necessarily where you think it is, it could be some other piece of code that you thought was OK.

To eliminate / investigate this possibility I suggest you rewrite your if statement something along the lines of:- if 1 equals 1 then return 10 otherwise return 100 and see if your code works.

 

[namliam]

how about simply sorting by the real date/time field to prevent this nonsense?

 

[arnelgp]

is this the whole query statement, maybe somewhere else you may take a look.

 

[neilduffy1024]

Hi,

Yeah I tried going back to real basics, so I ran it for iif(1=1,1,0) and it ran fine. See screenshot MS Access no_error.

However, as soon as I entered a tag, iif([Delivery_hour]=1, 1,0), it fell over - See MS Access error. This is really where I got lost. I wasn't sure if it was some peculiar MS access issue with iifs. I tried running it for the same tag, [Delivery_hour], without embedding it in an iif statement and it runs fine, so it doesn't seem to be the tag itself.

Utterly stumped!

 

[Uncle Gizmo]

Try the following two examples and Report the results please:-

[DELIVERY_HOUR] & [DELIVERY_HOUR]

And then try:-

[DELIVERY_HOUR] + [DELIVERY_HOUR]

 

[arnelgp]

does csv_Actuals table has delivery_hour field? if not then remove csv_Actuals from table on your expression and qualify if, ie:


IIf([xml_EM_ResPlan].[DELIVERY_HOUR]<8,[[xml_EM_ResPlan].[DELIVERY_HOUR]+24, [xml_EM_ResPlan].[DELIVERY_HOUR])

or
IIf([xml_EM_StemResultPartInfo].[DELIVERY_HOUR]<8,[xml_EM_StemResultPartInfo].[DELIVERY_HOUR]+24, [xml_EM_StemResultPartInfo].[DELIVERY_HOUR])

 

[neilduffy1024]

Ok, I hope I understood correctly, so I tried running the test: [Delivery_hour] + [Delivery_hour], but unfortunately it gives me the 'could refer to more than one table error' - see attached. It is the same error for both scenarios (+ and &), I suspect I may have misunderstood.

Arnelgp, csv_actuals table does have a delivery_hour field. It just wasn't visible in the screenshot I gave. I did try it for all the tables (they all contain that same field, which I linked to align data).

Thanks guys.

 

[Uncle Gizmo]

Try:-

[csv_Actuals]![DELIVERY_HOUR] + [csv_Actuals]![DELIVERY_HOUR]

 

[neilduffy1024]

Ok, cool, so that enabled me to run both scenarios. Both ran as I would have expected in the likes of excel.

Say [Delivery_Hour] = 15:

[Delivery_Hour] & [Delivery_Hour] = 1515
[Delivery_Hour] + [Delivery_Hour] = 30

Cheers

 

[Uncle Gizmo]

Actually I'm showing the wrong table qualifier in my previous post, arnelgp has it sussed see:-

does csv_Actuals table has delivery_hour field? if not then remove csv_Actuals from table on your expression and qualify if, ie:


IIf([xml_EM_ResPlan].[DELIVERY_HOUR]<8,[[xml_EM_ResPlan].[DELIVERY_HOUR]+24, [xml_EM_ResPlan].[DELIVERY_HOUR])

or
IIf([xml_EM_StemResultPartInfo].[DELIVERY_HOUR]<8,[xml_EM_StemResultPartInfo].[DELIVERY_HOUR]+24, [xml_EM_StemResultPartInfo].[DELIVERY_HOUR])

 

[neilduffy1024]

Genius, thanks chaps!!

I re-ran the original iif statement but with csv_Actuals before all the [Delivery_Hour] tags and it worked perfectly!

Thank you both very much for your help, much appreciated, that was driving me daft!

 

[namliam]

how about simply sorting by the real date/time field to prevent this nonsense?

Well, guess my suggestion went into the bin

 

[Uncle Gizmo]

Well, guess my suggestion went into the bin

I think your suggestion has merit.

Your suggestion might have been taken more seriously if you had worded it differently.

 

[neilduffy1024]

Namliam, sorry, didn't mean to omit. Three reasons I didn't do it: 1) I have to admit, I missed your comment. 2) as odd as it sounds, the real time/date field wasn't available. 3) I wanted to learn, this should have been a simple intro

 

[namliam]

1) Aaargh, the story of my life!
2) I am going to guess tradedate is the date part of the hour part?
3) Learing is always good though learning about a patch solution in a situation where a "more proper" solution might be available.... can be counter productive.