Need help with access query...

[pedie]

Hi I need help with access query...

Current query does not work...
I am trying to use Update query to update the record in table if TOTALSALES"<40 then TOTALSALES records value = TOTALSALES + 1

[/FONT]
[FONT=Courier New]UPDATE rawdata SET rawdata.TOTALSALES = "TOTALSALES" +  1
WHERE (("TOTALSALES"<40));

Please advice.
Pedie

 

[Norie]

Why is TOTALSALES in quotes?

It is a field isn't it?

If it is then it should definitely not have quotes, you should be able to use it on it's own.

Mind you wrapping it in [] wouldn't to any harm.

 

[pedie]

Hi Norie, total Sales in the number from 20 - 190
No blanks.
I want query to check the Total Sales Col then add 1 if Total Sales < 40


Thanks Norie

 

[Norie]

That still doesn't explain why TOTALSALES is in quotes.

What is TOTALSALES? A field? The sum of another field (perhaps SALES)?

Try this:

UPDATE RawData SET RawData.TOTALSALES = [rawdata].[TOTALSALES]+1
WHERE (((RawData.TOTALSALES)<40));

 

[xenou]

just to reinforce Norie's instruction, it never hurts to bracket table and column names:

UPDATE [rawdata] 
SET [rawdata].[TOTALSALES] = [TOTALSALES] +  1
WHERE [TOTALSALES] < 40;

You can leave them off if there are no spaces in the table or column names:

UPDATE rawdata 
SET rawdata.TOTALSALES = TOTALSALES +  1
WHERE TOTALSALES < 40;

If any table or column has a space you must bracket it:

SELECT Table1.[Field 1] From Table;

 

[Norie]

pedie

Did Access add the quotes itself when you typed TOTALSALES in the update to section?

 

[pedie]

pedie

Did Access add the quotes itself when you typed TOTALSALES in the update to section?

_ Yes it did...

Norie, thanks alot! Xen thanks for making additions to it...

Both works great..!!!

 

[Norie]

Pedie

I actually forgot that Access sometimes did that, and it doesn't if you specify the table name which you need to do with some databases.

 

[pedie]

Pedie

I actually forgot that Access sometimes did that, and it doesn't if you specify the table name which you need to do with some databases.

Okay Norie...Thanks again!!!