Problems using Docmd.RunSQL

[Marlon Smith-Williams via AccessMonster.com]

I am trying to produce some vb code that will query table1 in database1 and
write the results to table1 in database2. When I run the following I get a
message:

"Wrong number of arguments used with function in query expression - 3075"
using Access 2000. The code breaks at the first isnull function. Can anyone
point me in the right direction to resolving this. It would be grately
appreciated.

-----CODE------

DoCmd.RunSQL "INSERT INTO [dbo_LOCALEXTRACTION]" _
& "(datasource , datasourceref , seqrefnum , organisation , firstname ,
lastname , address1 ,address2 , address3 , address4 , town , county ,
postcode , email , telephone , mobile , fax , deceasedflag , goneawayflag ,
dataprotectionflag)" _
& "SELECT NULL as datasource , [dbo_SUPPORTER].suppID as datasourceref,
isnull([dbo_COMMUNICATIONS].commonurn, NULL) as seqrefnum, isnull
(organisation , 'None Available') as organisation , isnull(firstname ,
'None Available') as firstname ," _
& "isnull(lastname , 'None Available') as lastname , isnull(AddressLine1 ,
'None Available') as address1 , isnull(AddressLine2 , 'None Available') as
address2 , isnull(AddressLine3 , 'None Available') as address3 , isnull
(AddressLine4 , 'None Available') as address4 , isnull(Town , 'None
Available') as town , isnull(County , 'None Available') as county , isnull
(Postcode , 'None Available') as postcode , isnull(emailAddress , 'None
Available') as email ," _
& "isnull(TelephoneNumber , 'None Available') as telephone , isnull
(mobileNumber , 'None Available') as mobile , isnull(faxNumber , 'None
Available') as fax , [dbo_SUPPORTER].deceased as deceasedflag ,
[dbo_SUPPORTER].goneaway as goneaway , [dbo_SUPPORTER].dataProtection as
dataprotectionflag FROM [dbo_COMMUNICATIONS] right join [dbo_SUPPORTER] ON
[dbo_COMMUNICATIONS].suppid = [dbo_SUPPORTER].suppid where [dbo_SUPPORTER]
..emailaddress LIKE" & str_SearchEmail & ""

 

[JohnFol]

isnull(TelephoneNumber , 'None Available') is not a valid statement. The
syntax is

Syntax

IsNull(expression)








"Marlon Smith-Williams via AccessMonster.com"

 

[Dirk Goldgar]

"Marlon Smith-Williams via AccessMonster.com"

I am trying to produce some vb code that will query table1 in
database1 and write the results to table1 in database2. When I run
the following I get a message:

"Wrong number of arguments used with function in query expression -
3075" using Access 2000. The code breaks at the first isnull
function. Can anyone point me in the right direction to resolving
this. It would be grately appreciated.

-----CODE------

DoCmd.RunSQL "INSERT INTO [dbo_LOCALEXTRACTION]" _
& "(datasource , datasourceref , seqrefnum , organisation , firstname
, lastname , address1 ,address2 , address3 , address4 , town , county
, postcode , email , telephone , mobile , fax , deceasedflag ,
goneawayflag , dataprotectionflag)" _
& "SELECT NULL as datasource , [dbo_SUPPORTER].suppID as
datasourceref, isnull([dbo_COMMUNICATIONS].commonurn, NULL) as
seqrefnum, isnull (organisation , 'None Available') as organisation ,
isnull(firstname , 'None Available') as firstname ," _
& "isnull(lastname , 'None Available') as lastname ,
isnull(AddressLine1 , 'None Available') as address1 ,
isnull(AddressLine2 , 'None Available') as address2 ,
isnull(AddressLine3 , 'None Available') as address3 , isnull
(AddressLine4 , 'None Available') as address4 , isnull(Town , 'None
Available') as town , isnull(County , 'None Available') as county ,
isnull (Postcode , 'None Available') as postcode ,
isnull(emailAddress , 'None Available') as email ," _ &
"isnull(TelephoneNumber , 'None Available') as telephone , isnull
(mobileNumber , 'None Available') as mobile , isnull(faxNumber ,
'None Available') as fax , [dbo_SUPPORTER].deceased as deceasedflag
, [dbo_SUPPORTER].goneaway as goneaway ,
[dbo_SUPPORTER].dataProtection as dataprotectionflag FROM
[dbo_COMMUNICATIONS] right join [dbo_SUPPORTER] ON
[dbo_COMMUNICATIONS].suppid = [dbo_SUPPORTER].suppid where
[dbo_SUPPORTER] .emailaddress LIKE" & str_SearchEmail & ""

As JohnFol has pointed out, you're not using IsNull() correctly -- your
syntax is reminiscent of the T-SQL ISNULL function, but the Access
IsNull() function isn't the same. The Nz() function looks like the one
you should be using.

 

[Marlon Smith-Williams via AccessMonster.com]

That is the thing. I am trying to use the SQL isnull funcion. It is part of
the docmd.runsql. I can use other SQL server functions like getdate(). The
isnull one will not work. Is it because the same function name exists in
the VBA, I thought enclosing it in speech marks would eradicate this
problem ??

 

[Dirk Goldgar]

"Marlon Smith-Williams via AccessMonster.com" <[email protected]>
wrote in message

That is the thing. I am trying to use the SQL isnull funcion. It is
part of the docmd.runsql. I can use other SQL server functions like
getdate(). The isnull one will not work. Is it because the same
function name exists in the VBA, I thought enclosing it in speech
marks would eradicate this problem ??

Is this statement being executed in an ADP or in an MDB?

 

[Marlon Smith-Williams via AccessMonster.com]

MDB... on data retrieval I want to replace NULL values with the string
'None Available' . The data is sitting in a datawarehouse, my access
application queries the DW and takes the result set and places it into a
temporary table within the access application. Once the user has completed
modifying a particular record the changed information is posted to a table
in a seperate DB. This information is then processed and checked/clean by a
team.

 

[Dirk Goldgar]

"Marlon Smith-Williams via AccessMonster.com" <[email protected]>
wrote in message

MDB... on data retrieval I want to replace NULL values with the string
'None Available' . The data is sitting in a datawarehouse, my access
application queries the DW and takes the result set and places it
into a temporary table within the access application. Once the user
has completed modifying a particular record the changed information
is posted to a table in a seperate DB. This information is then
processed and checked/clean by a team.

If you are running this SQL statement in an MDB via RunSQL, then your
query must use Jet SQL syntax, and I would not expect any of the T-SQL
functions to work. You say you've used GetDate successfully, but the
only way I can think of that happening is if you defined a VBA function
of the same name (unless maybe you used it in a pass-through query).
ISNULL is a T-SQL function, but there's also an IsNull VBA function of
the same name, and that's the one that your query is actually executing.
Unfortunately, the VBA function doesn't do the same thing as the T-SQL
function.

The solution is to use the Nz() function instead, which is almost
identical to the T-SQL ISNULL function.