Giorgio's query suggestion needs to be refined a bit.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there!

Thanks so much to Giorgio and Michael who replied to my posting yesterday.
I was given an excellent suggestion yesterday on how to streamline my
queries and was given the following suggestion to run the following 6 queries
in my code:

Update tblFinalData
SET GSTRatio = IIF(IsNull([GSTRation]),0,GSTRatio), _
HSTRatio = IIF(IsNull(HSTRatio),0,HSTRatio), _
QSTRatio = IIF(IsNull(QSTRatio),0,QSTRatio), _
Wildcard1 = IIF(IsNull(Wildcard1),"Empty",Wildcard1), _
Wildcard2 = IIF(IsNull(Wildcard2),"Empty",Wildcard2), _
Wildcard3 = IIf(IsNull(Wildcard3), "Empty", Wildcard3)

However, I get an "Expected End of Statement" error with the comma before
the line continuation character being highlighted in the second row of this
code. Am I missing a bracket somewhere or a comma or are there too many
commas? I've tried this with and without the line continuation character and
it still doesn't seem to work.

Thanks!

JR
 
I'm not sure if you are trying this SQL in code or in query
If in code, then try this
"Update tblFinalData " & _
" SET GSTRatio = IIF(IsNull([GSTRation]),0,GSTRatio), " & _
" HSTRatio = IIF(IsNull(HSTRatio),0,HSTRatio), " & _
" QSTRatio = IIF(IsNull(QSTRatio),0,QSTRatio), " & _
" Wildcard1 = IIF(IsNull(Wildcard1),"Empty",Wildcard1), " & _
" Wildcard2 = IIF(IsNull(Wildcard2),"Empty",Wildcard2), " & _
"Wildcard3 = IIf(IsNull(Wildcard3), "Empty", Wildcard3)"

Mybe that what you tried to do,
I can't see any thing wrong with the SQL
--
I hope that helped
Good Luck


Johnny Bright said:
Hi Ofer,

Thanks so much for the help! Ok, I've removed the "_" and left the commas
but am still getting the expected end of statement thing. I tried removing
the commas, the code went from red back to red and black and so I thought I
was all set but then I get a "variable not defined" error pointing at
tblFinalData. I enclose tblFinalData in quotes and then get "Sub or function
not defined" pointing at "Update". I know this will work, I'm just not so
good with queries in code.

Thanks again!

JR
--
www.brightfuture.ca/bright
My email address can be found on my site.


Ofer said:
Are you using this SQL in a query?
If so, remove the _ from the Ens of the line

Update tblFinalData
SET GSTRatio = IIF(IsNull([GSTRation]),0,GSTRatio),
HSTRatio = IIF(IsNull(HSTRatio),0,HSTRatio),
QSTRatio = IIF(IsNull(QSTRatio),0,QSTRatio),
Wildcard1 = IIF(IsNull(Wildcard1),"Empty",Wildcard1),
Wildcard2 = IIF(IsNull(Wildcard2),"Empty",Wildcard2),
Wildcard3 = IIf(IsNull(Wildcard3), "Empty", Wildcard3)

--
I hope that helped
Good Luck


Johnny Bright said:
Hi there!

Thanks so much to Giorgio and Michael who replied to my posting yesterday.
I was given an excellent suggestion yesterday on how to streamline my
queries and was given the following suggestion to run the following 6 queries
in my code:

Update tblFinalData
SET GSTRatio = IIF(IsNull([GSTRation]),0,GSTRatio), _
HSTRatio = IIF(IsNull(HSTRatio),0,HSTRatio), _
QSTRatio = IIF(IsNull(QSTRatio),0,QSTRatio), _
Wildcard1 = IIF(IsNull(Wildcard1),"Empty",Wildcard1), _
Wildcard2 = IIF(IsNull(Wildcard2),"Empty",Wildcard2), _
Wildcard3 = IIf(IsNull(Wildcard3), "Empty", Wildcard3)

However, I get an "Expected End of Statement" error with the comma before
the line continuation character being highlighted in the second row of this
code. Am I missing a bracket somewhere or a comma or are there too many
commas? I've tried this with and without the line continuation character and
it still doesn't seem to work.

Thanks!

JR
Click to expand...
Click to expand...
Click to expand...
 
Hi Ofer,

Thanks so much for the help! Ok, I've removed the "_" and left the commas
but am still getting the expected end of statement thing. I tried removing
the commas, the code went from red back to red and black and so I thought I
was all set but then I get a "variable not defined" error pointing at
tblFinalData. I enclose tblFinalData in quotes and then get "Sub or function
not defined" pointing at "Update". I know this will work, I'm just not so
good with queries in code.

Thanks again!

JR
--
www.brightfuture.ca/bright
My email address can be found on my site.


Ofer said:
Are you using this SQL in a query?
If so, remove the _ from the Ens of the line

Update tblFinalData
SET GSTRatio = IIF(IsNull([GSTRation]),0,GSTRatio),
HSTRatio = IIF(IsNull(HSTRatio),0,HSTRatio),
QSTRatio = IIF(IsNull(QSTRatio),0,QSTRatio),
Wildcard1 = IIF(IsNull(Wildcard1),"Empty",Wildcard1),
Wildcard2 = IIF(IsNull(Wildcard2),"Empty",Wildcard2),
Wildcard3 = IIf(IsNull(Wildcard3), "Empty", Wildcard3)

--
I hope that helped
Good Luck


Johnny Bright said:
Hi there!

Thanks so much to Giorgio and Michael who replied to my posting yesterday.
I was given an excellent suggestion yesterday on how to streamline my
queries and was given the following suggestion to run the following 6 queries
in my code:

Update tblFinalData
SET GSTRatio = IIF(IsNull([GSTRation]),0,GSTRatio), _
HSTRatio = IIF(IsNull(HSTRatio),0,HSTRatio), _
QSTRatio = IIF(IsNull(QSTRatio),0,QSTRatio), _
Wildcard1 = IIF(IsNull(Wildcard1),"Empty",Wildcard1), _
Wildcard2 = IIF(IsNull(Wildcard2),"Empty",Wildcard2), _
Wildcard3 = IIf(IsNull(Wildcard3), "Empty", Wildcard3)

However, I get an "Expected End of Statement" error with the comma before
the line continuation character being highlighted in the second row of this
code. Am I missing a bracket somewhere or a comma or are there too many
commas? I've tried this with and without the line continuation character and
it still doesn't seem to work.

Thanks!

JR
Click to expand...
Click to expand...
 
Hi there!

Thanks so much to Giorgio and Michael who replied to my posting yesterday.
I was given an excellent suggestion yesterday on how to streamline my
queries and was given the following suggestion to run the following 6 queries
in my code:

Update tblFinalData
SET GSTRatio = IIF(IsNull([GSTRation]),0,GSTRatio), _
HSTRatio = IIF(IsNull(HSTRatio),0,HSTRatio), _
QSTRatio = IIF(IsNull(QSTRatio),0,QSTRatio), _
Wildcard1 = IIF(IsNull(Wildcard1),"Empty",Wildcard1), _
Wildcard2 = IIF(IsNull(Wildcard2),"Empty",Wildcard2), _
Wildcard3 = IIf(IsNull(Wildcard3), "Empty", Wildcard3)

However, I get an "Expected End of Statement" error with the comma before
the line continuation character being highlighted in the second row of this
code. Am I missing a bracket somewhere or a comma or are there too many
commas? I've tried this with and without the line continuation character and
it still doesn't seem to work.
Click to expand...

Where are you running this? VBA and SQL are two different languages,
and the query engine doesn't understand VBA, while the VBA compiler
doesn't know SQL. It looks like you're trying to execute this SQL
query from VBA. Won't work!

Put it (without the underscores, which are not used in SQL) into the
SQL window of a new query and it should work fine.

John W. Vinson[MVP]
 
Are you using this SQL in a query?
If so, remove the _ from the Ens of the line

Update tblFinalData
SET GSTRatio = IIF(IsNull([GSTRation]),0,GSTRatio),
HSTRatio = IIF(IsNull(HSTRatio),0,HSTRatio),
QSTRatio = IIF(IsNull(QSTRatio),0,QSTRatio),
Wildcard1 = IIF(IsNull(Wildcard1),"Empty",Wildcard1),
Wildcard2 = IIF(IsNull(Wildcard2),"Empty",Wildcard2),
Wildcard3 = IIf(IsNull(Wildcard3), "Empty", Wildcard3)
 
Back
Top