Handling Empty Strings in Query

[Guest]

Hopefully this will be an easy one for someone..........

I have a stored parameter query (named exec SPQ_TS_Ins) that I want to
execute from elsewhere (Excel). The SQL is:

INSERT INTO BbookedHrs ( YrWeekNo, JobNo, TaskNo, Mmonday, Ttuesday,
Wwednesday, Tthursday, Ffriday, SatSun )
VALUES ([P1], [P2], [P3], [P4], [P5], [P6], [P7], [P8], [P9]);

and the VBA in Excel is:

Conn.Execute "exec SPQ_TS_Ins " & _
Range("weekno").Value & "," & Range("jobno" & r).Value & ","
& _
Range("taskno" & r).Value & "," & Range("mon" & r).Value &
"," & _
Range("tue" & r).Value & "," & Range("wed" & r).Value & ","
& _
Range("thu" & r).Value & "," & Range("fri" & r).Value & ","
& _
Range("we" & r).Value

where Conn is the ADO connection to the database and the parameters are the
evaluation of worksheet cells.

This works just fine except when some of the cells in Excel are left blank
at which point the SQL fails. Any ideas how I can handle the situation when
cells are blank?

All contributions greatly appreciated.

Thanks

 

[kingston via AccessMonster.com]

Do you know if the SQL fails because the table you're trying to insert into
does not allow nulls? If so, you can change the field propeties or use the
function Nz() to replace nulls.

Hopefully this will be an easy one for someone..........

I have a stored parameter query (named exec SPQ_TS_Ins) that I want to
execute from elsewhere (Excel). The SQL is:

INSERT INTO BbookedHrs ( YrWeekNo, JobNo, TaskNo, Mmonday, Ttuesday,
Wwednesday, Tthursday, Ffriday, SatSun )
VALUES ([P1], [P2], [P3], [P4], [P5], [P6], [P7], [P8], [P9]);

and the VBA in Excel is:

Conn.Execute "exec SPQ_TS_Ins " & _
Range("weekno").Value & "," & Range("jobno" & r).Value & ","
& _
Range("taskno" & r).Value & "," & Range("mon" & r).Value &
"," & _
Range("tue" & r).Value & "," & Range("wed" & r).Value & ","
& _
Range("thu" & r).Value & "," & Range("fri" & r).Value & ","
& _
Range("we" & r).Value

where Conn is the ADO connection to the database and the parameters are the
evaluation of worksheet cells.

This works just fine except when some of the cells in Excel are left blank
at which point the SQL fails. Any ideas how I can handle the situation when
cells are blank?

All contributions greatly appreciated.

Thanks

 

[Guest]

The erro message returned by VBA when stepping through the code is:

Run-time error '-2147217900(80040e14)':

Syntax error in parameters clause. Make sure the parameter exists and that
you typed its value correctly.

I'm not sure about the table not accepting nulls. The first 3 fields are
text and the property "Allow Zero Length" is set to Yes in all cases. The
other 6 fields are all number fields and the "Required" property is set to
No. Where do I find the field property to allow nulls?

The Nz() function sounds interesting but I've never seen it before. How
would I go about using it?

Thanks for your response

Do you know if the SQL fails because the table you're trying to insert into
does not allow nulls? If so, you can change the field propeties or use the
function Nz() to replace nulls.

Hopefully this will be an easy one for someone..........

I have a stored parameter query (named exec SPQ_TS_Ins) that I want to
execute from elsewhere (Excel). The SQL is:

INSERT INTO BbookedHrs ( YrWeekNo, JobNo, TaskNo, Mmonday, Ttuesday,
Wwednesday, Tthursday, Ffriday, SatSun )
VALUES ([P1], [P2], [P3], [P4], [P5], [P6], [P7], [P8], [P9]);

and the VBA in Excel is:

Conn.Execute "exec SPQ_TS_Ins " & _
Range("weekno").Value & "," & Range("jobno" & r).Value & ","
& _
Range("taskno" & r).Value & "," & Range("mon" & r).Value &
"," & _
Range("tue" & r).Value & "," & Range("wed" & r).Value & ","
& _
Range("thu" & r).Value & "," & Range("fri" & r).Value & ","
& _
Range("we" & r).Value

where Conn is the ADO connection to the database and the parameters are the
evaluation of worksheet cells.

This works just fine except when some of the cells in Excel are left blank
at which point the SQL fails. Any ideas how I can handle the situation when
cells are blank?

All contributions greatly appreciated.

Thanks

 

[kingston via AccessMonster.com]

I can't decipher the error message but you can use the Nz() function as such:

Instead of [P1], use Nz([P1],Value if [P1] is null). The value can be a
space or a 0. Hopefully that doesn't cause some other data conflict.

The erro message returned by VBA when stepping through the code is:

Run-time error '-2147217900(80040e14)':

Syntax error in parameters clause. Make sure the parameter exists and that
you typed its value correctly.

I'm not sure about the table not accepting nulls. The first 3 fields are
text and the property "Allow Zero Length" is set to Yes in all cases. The
other 6 fields are all number fields and the "Required" property is set to
No. Where do I find the field property to allow nulls?

The Nz() function sounds interesting but I've never seen it before. How
would I go about using it?

Thanks for your response

Do you know if the SQL fails because the table you're trying to insert into
does not allow nulls? If so, you can change the field propeties or use the

[quoted text clipped - 32 lines]

 

[Guest]

OK,

I tried the Nz() function and the SQL worked great when I ran it from
Access, but failed when I ran the VBA (from Excel). I then removed the Nz()
function and got the same results (I hadn't previously tried running it
solely in Access). This led me to look again at the VBA and it appears that
this is where the problem lies. I changed the code so that each parameter
has a conversion to the appropriate type as follows:

Conn.Execute "exec SPQ_TS_Ins '" & _
Range("weekno").Value & "','" & Range("jobno" & r).Value & "','" & _
Range("taskno" & r).Value & "'," & CSng(Range("mon" & r).Value) & "," & _
CSng(Range("tue" & r).Value) & "," & CSng(Range("wed" & r).Value) & "," & _
CSng(Range("thu" & r).Value) & "," & CSng(Range("fri" & r).Value) & "," & _
CSng(Range("we" & r).Value)

The code is now reliable, but I get zero's in the number fields when the
spreadsheet cells are unpopulated. If I use the following VBA (without the
SQL):

With Rs
.AddNew ' create a new record
'add values to each field in the record
.Fields("YrWeekNo") = Range("weekno").Value
.Fields("JobNo") = Range("jobno" & r).Value
.Fields("TaskNo") = Range("taskno" & r).Value
.Fields("Mmonday") = Range("mon" & r).Value
.Fields("Ttuesday") = Range("tue" & r).Value
.Fields("Wwednesday") = Range("wed" & r).Value
.Fields("Tthursday") = Range("thu" & r).Value
.Fields("Ffriday") = Range("fri" & r).Value
.Fields("SatSun") = Range("we" & r).Value
.Update ' stores the new record
End With

(where RS is an ADO recordset object) it will quite happily run with blank
cells in the spreadsheet and leave null values in the DB (which is what I
want).

This would be fine in a local Excel / Access environment, but I have been
using this as a development platform for an ASP website I'm working on. In
this environment the security of the stored query relative to dynamic SQl is
important. I suppose I could just write some code to strip out zero's when
recalling data from Access, but if there is a way I would prefer not to store
them in the first place.

Nice tip about the Nz() function - I played around with that and I can
definitely find some uses for it.

I'm wondering now if I should take this question to a VBA forum?

Thanks again for your help.

I can't decipher the error message but you can use the Nz() function as such:

Instead of [P1], use Nz([P1],Value if [P1] is null). The value can be a
space or a 0. Hopefully that doesn't cause some other data conflict.

The erro message returned by VBA when stepping through the code is:

Run-time error '-2147217900(80040e14)':

Syntax error in parameters clause. Make sure the parameter exists and that
you typed its value correctly.

I'm not sure about the table not accepting nulls. The first 3 fields are
text and the property "Allow Zero Length" is set to Yes in all cases. The
other 6 fields are all number fields and the "Required" property is set to
No. Where do I find the field property to allow nulls?

The Nz() function sounds interesting but I've never seen it before. How
would I go about using it?

Thanks for your response

Do you know if the SQL fails because the table you're trying to insert into
does not allow nulls? If so, you can change the field propeties or use the

[quoted text clipped - 32 lines]

Thanks