Number of query values and destination fields are not the same

[AlexN]

Hi everybody,

I use this line of code to insert records in a table

CurrentDb.Execute "INSERT INTO tblTransactions ([Account1ID], [Account2ID], [TransactionDate], [TransactionType], [TransactionCategory], [Amount])" & _
" VALUES ( " & Me.[Account2ID] & " , " & Me.[Account1ID] & " , #" & Format(Me.[TransactionDate], "mm/dd/yyyy") & "# , '" & Me.[TransactionType] & "', '" & "Transfer From" & "' , " & Me.[Amount] & ") ; "

When Amount is integer everything works perfectly alright, but when Amount is decimal, code does not execute and I get the error message "Number of query values and destination fields are not the same". Needless to mention that Amount field is defined as currency with two decimal digits.

Any ideas?

Thank you

 

[CJ_London]

suggest put a line before your execute line to debug.print the sql, then copy and paste the result into a new query to see the result

 

[AlexN]

Thank you for your answer but I'm afraid I don't know how to do that.

I mean I wrote the line you suggested, before the "CurrentDb.Execute line, and as far as I know that should bring a result in the Immediate Window. But every time I try to pass a new record it brings up the previously mentioned error window and when I press the Debug button nothing appears in the Immediate Window.

 

[Grumm]

The line must be this :

Debug.print "INSERT INTO tblTransactions ([Account1ID], [Account2ID], [TransactionDate], [TransactionType], [TransactionCategory], [Amount])" & _
" VALUES ( " & Me.[Account2ID] & " , " & Me.[Account1ID] & " , #" & Format(Me.[TransactionDate], "mm/dd/yyyy") & "# , '" & Me.[TransactionType] & "', '" & "Transfer From" & "' , " & Me.[Amount] & ") ; "

Then place a breakpoint right after this line. Run the code and it will automaticaly stop and you can see what is going on at that moment.

 

[AlexN]

The line must be this :

Debug.print "INSERT INTO tblTransactions ([Account1ID], [Account2ID], [TransactionDate], [TransactionType], [TransactionCategory], [Amount])" & _
" VALUES ( " & Me.[Account2ID] & " , " & Me.[Account1ID] & " , #" & Format(Me.[TransactionDate], "mm/dd/yyyy") & "# , '" & Me.[TransactionType] & "', '" & "Transfer From" & "' , " & Me.[Amount] & ") ; "

Then place a breakpoint right after this line. Run the code and it will automaticaly stop and you can see what is going on at that moment.

Well it obviously has something to do with the commas. Decimal numbers have comma and code sees the integer part as a value for a field, and the decimal part as another value for some (non existing) field. Any ideas?

 

[CJ_London]

what does your sql look like in the immediate window?

 

[Grumm]

You can just format the decimal field.
Format(Me.[Amount],"Standard")

But if the amount is not a decimal then you still get errors.

 

[CJ_London]

I presume the amount field in your transactions table is double or currency?

a decimal number is 12345.678, not 12,345.678 which is a formatted view of the value and so may have been converted to text

 

[AlexN]

what does your sql look like in the immediate window?

Trying to insert an amount of 2,4 Euros the sql was :

INSERT INTO tblTransactions ([Account1ID], [Account2ID], [TransactionDate], [TransactionType], [TransactionCategory], [Amount]) VALUES ( 6 , 1 , #02/02/2015# , 'Transfer', 'Transfer From' , 2,4) ;

 

[AlexN]

I presume the amount field in your transactions table is double or currency?

a decimal number is 12345.678, not 12,345.678 which is a formatted view of the value and so may have been converted to text

It is currency (two decimal digits), and my input will always be with two decimal digits (except integers).

 

[CJ_London]

think this has to be a language issue since access expects 2.4 as a value

The problem is that your amount field is in a text box so it is being treated as text, there was another thread recently with a similar issue. You could try setting the format property of the control to currency or double which may force it to treat it as a number but as with converting dates to US style I think you need to do the same for numbers, so I would give Grumms suggestion a try.

If your form was bound to the various transaction fields, which is the normal way for entering data, I don't think you would have a problem

 

[AlexN]

think this has to be a language issue since access expects 2.4 as a value

The problem is that your amount field is in a text box so it is being treated as text, there was another thread recently with a similar issue. You could try setting the format property of the control to currency or double which may force it to treat it as a number but as with converting dates to US style I think you need to do the same for numbers, so I would give Grumms suggestion a try.

If your form was bound to the various transaction fields, which is the normal way for entering data, I don't think you would have a problem

My form is bound to tblTransactions mentioned in the code, the form's Amount control property is currency, and I don't know how to convert currency to US style like I did with dates .

 

[CJ_London]

if it is bound - why are you running an append query? it happens automatically

 

[AlexN]

if it is bound - why are you running an append query? it happens automatically

It should be obvious from the code line :

For every transfer transaction from one account to another, there's a subtraction from Account1 and an addition to Account2. First part happens automaticallly second doesn't. That why the append...

 

[CJ_London]

hmm - dangerous - what happens if the append fails? your accounts will be out of balance. Besides which you don't need the second record. If you want to know the balance of an account you would use a query something like

SELECT A.AccountID, sum(DR.Amount) as Debit, sum(CR.Amount) as Credit
FROM (tblAccounts A LEFT JOIN tblTransactions DR ON A.AccountID=DR.Account1ID) LEFT JOIN tblTransactions CR ON A.AccountID=CR.Account2ID

But back to your original question - have your tried Grumm's suggestion. If not, do so, if you have, what was the outcome?

 

[AlexN]

hmm - dangerous - what happens if the append fails? your accounts will be out of balance. Besides which you don't need the second record. If you want to know the balance of an account you would use a query something like



But back to your original question - have your tried Grumm's suggestion. If not, do so, if you have, what was the outcome?

Tried all possible formats for the Amount value, all failed. Same old error message, still nothing appending.

As for your suggestion for the account balance, helpful enough but I'm more comfortable with mine, and the whole database is built on this idea.
However I'll give it a try, thanks

 

[CJ_London]

only other 'format' suggestion I can make is

replace(Me.[Amount] & "",",",".")

 

[AlexN]

only other 'format' suggestion I can make is

replace(Me.[Amount] & "",",",".")

I'll try this too, but I'm curious on how the amount value will be appended in the table field which accepts currency numbers.

For the time being I restricted inputs to integers only (rounding numbers).

 

[CJ_London]

but I'm curious on how the amount value will be appended in the table field which accepts currency numbers

look at the sql your vba code generated

VALUES ( 6 , 1 , #02/02/2015# , 'Transfer', 'Transfer From' , 2,4)

The # tell sql to interpret the characters between them as a date
the ' tell sql to interpret the characters between them as a string
no surrounding characters and it will be interpreted as a number - so you need to generate a sql string that looks like

VALUES ( 6 , 1 , #02/02/2015# , 'Transfer', 'Transfer From' , 2.4)

where 2.4 will be interpreted as a number

 

[AlexN]

look at the sql your vba code generated

VALUES ( 6 , 1 , #02/02/2015# , 'Transfer', 'Transfer From' , 2,4)

The # tell sql to interpret the characters between them as a date
the ' tell sql to interpret the characters between them as a string
no surrounding characters and it will be interpreted as a number - so you need to generate a sql string that looks like

VALUES ( 6 , 1 , #02/02/2015# , 'Transfer', 'Transfer From' , 2.4)

where 2.4 will be interpreted as a number

Yes but, I don't want 2.4 in the table field, I want 2,4 (in fact 2,40). The way the field is formatted it'll accept 2.4 as 24 (in fact 24,00 e). Haven't tried it yet though.

Forgot to mention that system (and access) use the Greek regional settings (thousands are separated with points(.) and decimal digits with commas (,).