SQL Problem - oh dear

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gina - 03 Mar 2005 21:44 GMT

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

Reply to this Message

Ken Snell [MVP] - 03 Mar 2005 22:06 GMT

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 & ")"

Signature

Ken Snell
<MS ACCESS MVP>

> Hi all.
>
[quoted text clipped - 24 lines]
> Thanks,
> Gina

Reply to this Message

Gina - 03 Mar 2005 22:46 GMT

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)

> You're not inserting the ' characters around the date text string (the
> datBill value):
[quoted text clipped - 33 lines]
> > Thanks,
> > Gina

Reply to this Message

Ken Snell [MVP] - 04 Mar 2005 00:48 GMT

Do a Debug.Print of the SQL variable after it's built. What does the text
string contain?

Signature

Ken Snell
<MS ACCESS MVP>

> Well, tried with and withouth ' characters
>
[quoted text clipped - 44 lines]
>> > Thanks,
>> > Gina

Reply to this Message

Gina - 06 Mar 2005 11:03 GMT

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

Reply to this Message

Ken Snell [MVP] - 06 Mar 2005 13:25 GMT

Glad to hear it!

Signature

Ken Snell
<MS ACCESS MVP>

> 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
[quoted text clipped - 3 lines]
>
> Gina

Reply to this Message

Tim Ferguson - 03 Mar 2005 23:04 GMT

"Gina" <gingingina@freenet.de> wrote in news:d080k0$cno$01$1@news.t-
online.com:

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

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

Reply to this Message

Gina - 06 Mar 2005 11:05 GMT

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

> "Gina" <gingingina@freenet.de> wrote in news:d080k0$cno$01$1@news.t-
> online.com:
[quoted text clipped - 11 lines]
>
> Tim F

Reply to this Message