IIf query: syntax error (comma) in query expression (1 Viewer)

neilduffy1024 · Aug 5, 2015

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 · Aug 5, 2015

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

neilduffy1024 · Aug 5, 2015

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 · Aug 5, 2015

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 · Aug 5, 2015

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

arnelgp · Aug 5, 2015

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

neilduffy1024 · Aug 5, 2015

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 · Aug 5, 2015

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

[DELIVERY_HOUR] & [DELIVERY_HOUR]

And then try:-

[DELIVERY_HOUR] + [DELIVERY_HOUR]

arnelgp · Aug 5, 2015

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 · Aug 5, 2015

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 · Aug 5, 2015

Try:-

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

neilduffy1024 · Aug 5, 2015

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 · Aug 5, 2015

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

arnelgp said:
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 · Aug 5, 2015

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 · Aug 5, 2015

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

Well, guess my suggestion went into the bin

Uncle Gizmo · Aug 5, 2015

namliam said:
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 · Aug 5, 2015

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 · Aug 5, 2015

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.

IIf query: syntax error (comma) in query expression (1 Viewer)

New member

Have been here a while

New member

Nifty Access Guy

The Mailman - AWF VIP

..forever waiting... waiting for jellybean!

New member

Attachments

Nifty Access Guy

..forever waiting... waiting for jellybean!

New member

Attachments

Nifty Access Guy

New member

Nifty Access Guy

New member

The Mailman - AWF VIP

Nifty Access Guy

New member

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread