SQL Problem - oh dear

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi all.

trying to insert some data ... it works fine with:

'SQL2 = "INSERT INTO tblBill(Nu, Dat, Sum, Toll, All, WorkID)
VALUES ( 123, '03.03.2005', 200, 20, 220,5)"

but doesn't work when reading the data from the form:

SQL= "INSERT INTO tblBill(Nu, Dat, Sum, Toll, All, WorkID) VALUES
( "
SQL= SQL & int_ReNum & ", " & datBill & ", " & Me.txtSum
SQL= SQL & ", " & Me.txtToll & ", " & Me.txtAll
SQL= SQL & ", " & Me.WorkID & ")"

Nu = number
Dat = text (I changed it from date to text)
Sum,Toll,All = Currency
WorkID = number

I am getting an error '3346' telling me that the number of values in the
query and target fields wouldn't match ! ????

I've tried numerous ways since hours ... can't find the bug
any idea & help would be great !!

Thanks,
Gina
 
You're not inserting the ' characters around the date text string (the
datBill value):

SQL= "INSERT INTO tblBill(Nu, Dat, Sum, Toll, All, WorkID) VALUES
( "
SQL= SQL & int_ReNum & ", '" & datBill & "', " & Me.txtSum
SQL= SQL & ", " & Me.txtToll & ", " & Me.txtAll
SQL= SQL & ", " & Me.WorkID & ")"
 
Well, tried with and withouth ' characters

both return the same error

number of arguments in query and target field don't match ( I am transkating
from german)
 
'SQL2 = "INSERT INTO tblBill(Nu, Dat, Sum, Toll, All, WorkID)
VALUES ( 123, '03.03.2005', 200, 20, 220,5)"
Click to expand...

Is "SUM" legal as a column name? Do you not get errors in SQL SELECT
statements? What about "ALL"?

SELECT ALL SUM
FROM Bill

seems to be asking for trouble...!



Tim F
 
Do a Debug.Print of the SQL variable after it's built. What does the text
string contain?
 
Solved it ... problem was that it was a 1:1 rel from 1 table to two others
.... so the problem was access tabledef , changed the table fields to be
text....and some other things

thanks Ken

Gina
 
Hi Tim,
I am using german expressions in my real db and in order to make it somehow
english I just took these 'forbidden' or 'problematic' expresssions.

Thanks , found the reason for my trouble sql
see previous answer

Gina
 
Back
Top