Why have i started getting Error 3075/compile Error Messages? 2

[ooch1]

Hello there,

I have a couple of queries that look at a ID field and if it was null then it would replace it with an alternative ID.
Up until today it has worked fine, but when i've gone to run these queries i now get either "<Message> in query expression <expression>. (Error 3075)" message.
I have a feeling it has something to do with it being a text field with number values as when i created a new NZ (GroupID) query i got a Compile error message.

Does anyone out there no why this is happening????

Many Thanks

OOch

 

[dhookom]

Could you share the sql view of the query as well as the field data types?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ooch1]

dhookom - I really hope you can help me out with this as this is starting to stress me out!

My code is as follows:

UPDATE [002-NBS_GAS_ROOTDATA_20040923] SET [002-NBS_GAS_ROOTDATA_20040923].NA_Ctrt_STATUS = nz(nz(Switch([002-NBS_GAS_ROOTDATA_20040923]![StandingCharge] Like "0.1193" Or [002-NBS_GAS_ROOTDATA_20040923]![StandingCharge] Like "0.1089","1-TARIFF"),Switch([002-NBS_GAS_ROOTDATA_20040923]![Contract end date] Like "*2004*","2-OPEN CONTRACT",[002-NBS_GAS_ROOTDATA_20040923]![Contract end date] Not Like "*2004*","3-CLOSED CONTRACT")),"2-OPEN CONTRACT");

The above field types are:
[002-NBS_GAS_ROOTDATA_20040923].NA_Ctrt_STATUS = text
[002-NBS_GAS_ROOTDATA_20040923]![StandingCharge] =Double
[002-NBS_GAS_ROOTDATA_20040923]![Contract end date] =Date

As i say, this worked until yesterday, i had an old version of the database and tried exporting the table and query and it worked fine in the prev db, but obviously things have moved on since that version.

OOch

 

[KenReay]

Hi

A couple of instant Observations

NZ(NZ( what is that supposed to be doing?

There is no WHERE keyword in your statement

Multiple Conditions need to be linked by AND or OR



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ooch1]

Ken

When the first switch is null then it marks it as "tariff", if it performs all the checks and is still null then "open contract".

It is an update query in access, so there is no WHERE clause just by default.

This still doesn't detract from the fact that this used to work and now doesn't, i have just imported all the queries and tables into a new db, but to no availe.

OOch

 

[KenReay]

Hi

To avoid comparing Dates to strings with like

UPDATE [002-NBS_GAS_ROOTDATA_20040923]
SET [002-NBS_GAS_ROOTDATA_20040923].NA_Ctrt_STATUS =nz( nz( Switch([002-NBS_GAS_ROOTDATA_20040923]![StandingCharge] Like "0.1193" Or [002-NBS_GAS_ROOTDATA_20040923]![StandingCharge] Like "0.1089","1-TARIFF"),Switch(Year([002-NBS_GAS_ROOTDATA_20040923]![Contract end date]) = 2004,"2-OPEN CONTRACT",Year([002-NBS_GAS_ROOTDATA_20040923]![Contract end date])<> 2004,"3-CLOSED CONTRACT")),"2-OPEN CONTRACT");

I am still unsure about the LIKE "0.1193" for a numeric column

Are you trying to run this in the query design window?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

What about something like this ?
UPDATE [002-NBS_GAS_ROOTDATA_20040923]
SET NA_Ctrt_STATUS = Switch(Int(10000*[StandingCharge])=1193 Or Int(10000*[StandingCharge])=1089,"1-TARIFF",Year([Contract end date]) <> 2004,"3-CLOSED CONTRACT",True,"2-OPEN CONTRACT");


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

Thanks for your help guys. I seem to have rectified the problem by importing the tables etc into a new db and starting again. Touch would everthing is working fine at the minute!!

OOch

 

[dhookom]

ooch1,
Do you understand the issues pointed out by others? If you don't have a clear understanding of comparing strings to numbers or dates and using "like" where you should be using "=" etc you will continue to have problems.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[PHV]

Another thing about Double that don't stores exact values.
In the immediate (debug) window (Ctrl+G when in VBE) type this:
? 1.045-1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

Duane - Point taken. Although to be fair i did try PHVs code and got the same comile error message.
I have replaced all the likes with = though, but if you could give a brief description of some of these problems then i would find it useful for future reference??

Many Thanks

OOch

 

[dhookom]

I would be careful about comparing the numbers to strings like [blue][Contract end date] Like "*2004*"[/blue].

Replace the above with [blue] Year([Contract end date]) =2004[/blue]. The new solution compares a number to a number.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]