More Variable Referencing Problems

[Guest]

Okay, here's another one for you...

When I pass this statement:

Insert into tblSCSalesOrders (OrderNumber, ServiceCall, SeriesNumber,
EnterDate, SubTotal, Tax, Freight, Total, Status, UserId, TaxRateCode,
TaxRate) Values ('" & gblOrderNumber & "',
[forms]![frmservicecalltimer]![txtservicecall], 1, date(), 0, 0, 0, 0, 2,
[forms]![subfrmuserinfo]![userid], ' & gblTaxRateID & ', ' & gblTaxRate & ');

I receive a Null Conversion error.

The variables are set as:

gblOrderNumber - String - current value "50144-1"
tblSCSalesOrders!OrderNumber - Text

gblTaxRateId - Single - current value 4
tblSCSalesOrders!TaxRateCode - Single

gblTaxCode - Double - current value 0
tblSCSalesOrders!TaxRate - Double

However, if I replace the variables, with their actual values, the statement
executes fine.

Any information on why, would be greatly appreciated.

Sharkbyte

 

[Jeff Boyce]

Just a possibility...

one/more of the Forms!... values is null or is evaluating to null?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Guest]

Jeff:

It would be, except for this:

Sharkbyte

Just a possibility...

one/more of the Forms!... values is null or is evaluating to null?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

Okay, here's another one for you...

When I pass this statement:

Insert into tblSCSalesOrders (OrderNumber, ServiceCall, SeriesNumber,
EnterDate, SubTotal, Tax, Freight, Total, Status, UserId, TaxRateCode,
TaxRate) Values ('" & gblOrderNumber & "',
[forms]![frmservicecalltimer]![txtservicecall], 1, date(), 0, 0, 0, 0, 2,
[forms]![subfrmuserinfo]![userid], ' & gblTaxRateID & ', ' & gblTaxRate & ');

I receive a Null Conversion error.

The variables are set as:

gblOrderNumber - String - current value "50144-1"
tblSCSalesOrders!OrderNumber - Text

gblTaxRateId - Single - current value 4
tblSCSalesOrders!TaxRateCode - Single

gblTaxCode - Double - current value 0
tblSCSalesOrders!TaxRate - Double

However, if I replace the variables, with their actual values, the statement
executes fine.

Any information on why, would be greatly appreciated.

Sharkbyte

 

[Guest]

Hi Sharkbyte,

What is the data type and value of gblTaxRate?

Try something like this:

Dim strSQL As String

strSQL = "Insert into tblSCSalesOrders " _
& "(OrderNumber, ServiceCall, SeriesNumber, EnterDate, SubTotal, " _
& "Tax, Freight, Total, Status, UserId, TaxRateCode,TaxRate) " _
& "Values ('" & gblOrderNumber & "', " _
& "'" & [Forms]![frmservicecalltimer]![txtservicecall] & "', " _
& "1, " & Date & ", 0, 0, 0, 0, 2, " _
& [Forms]![subfrmuserinfo]![UserID] & ", " _
& gblTaxRateId & ", " & gblTaxRate & ");"


This assumes that [Forms]![subfrmuserinfo]![UserID] represents a numeric
value available on another open form named subfrmuserinfo. If you are
referring to a subform on the current form, then I don't think you are doing
so correctly.

Hint: Print your SQL statement to the Immediate window, using this:

Debug.Print strSQL

You should have a valid SQL statement if all is good. This will often help
you pinpoint errors.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Okay, here's another one for you...

When I pass this statement:

Insert into tblSCSalesOrders (OrderNumber, ServiceCall, SeriesNumber,
EnterDate, SubTotal, Tax, Freight, Total, Status, UserId, TaxRateCode,
TaxRate) Values ('" & gblOrderNumber & "',
[forms]![frmservicecalltimer]![txtservicecall], 1, date(), 0, 0, 0, 0, 2,
[forms]![subfrmuserinfo]![userid], ' & gblTaxRateID & ', ' & gblTaxRate & ');

I receive a Null Conversion error.

The variables are set as:

gblOrderNumber - String - current value "50144-1"
tblSCSalesOrders!OrderNumber - Text

gblTaxRateId - Single - current value 4
tblSCSalesOrders!TaxRateCode - Single

gblTaxCode - Double - current value 0
tblSCSalesOrders!TaxRate - Double

However, if I replace the variables, with their actual values, the statement
executes fine.

Any information on why, would be greatly appreciated.

Sharkbyte

 

[Guest]

Tom:

gblTaxRate and gblTaxCode are the same variable. I simply referenced it
incorrectly.

However, your solution worked, and I thank you, kindly.

I've never built a SQL statement, as a string, first. It seems rather odd.
But as long as it works. =)

Sharkbyte

 

[Guest]

Hi Sharkbyte,

I'm glad to read that you got it working. I always build my SQL statements
in code using a string variable. That way, you can do a Debug.Print
VariableName, which is a very helpful troubleshooting technique.

I probably should have wrapped the date in pound sign delimiters (#), but as
long as it's working, then perhaps you don't need to do this.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Tom:

gblTaxRate and gblTaxCode are the same variable. I simply referenced it
incorrectly.

However, your solution worked, and I thank you, kindly.

I've never built a SQL statement, as a string, first. It seems rather odd.
But as long as it works. =)

Sharkbyte