Second pair of eyes to spot syntax error?

P

Peter Clancy

Hi,
I need a second pair of eyes to glance over this statement because I cannot
spot the syntax error in it.
The data is being saved into a table called Calldata which has the following
field definitions:
ANumber Text(30)
StartDate Text(15)
StartTime Text(10)
Duration Number(Decimal)
Value Currency
DestCode Text(10)
BNumber Text(30)


Dim ANumber As String
Dim BNumber As String
Dim DestCode As String
Dim StartDate As String
Dim StartTime As String
Dim DurMins As Single
Dim Value As Currency

Dim Comma As String
Dim DblQuote As String
Dim SglQuote As String

Comma = Chr(44)
DblQuote = Chr(34)
SglQuote = Chr(39)

The data is parsed from an input file and stored in the above variables
before creating the input string as below:

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration, Value,
DestCode, BNumber) " & _
"values(" & DblQuote & ANumber & DblQuote & Comma & _
DblQuote & StartDate & DblQuote & Comma & _
DblQuote & StartTime & DblQuote & Comma & _
DurMins & Comma & _
SglQuote & Value & SglQuote & Comma & _
DblQuote & DestCode & DblQuote & Comma & _
DblQuote & BNumber & DblQuote & ");"

A debug.print of InsertString shows:

debug.Print InsertString
INSERT INTO Calldata ( ANumber, StartDate, StartTime, Duration, Value,
DestCode, BNumber) values("02077384851
","01-Aug-2006","08:20",50.83333,'0.506',"020867","02086786396");

I have tried the currency field both with and without the single quote but
all I am getting is a syntax error in the insert statement and I cannot see
where. Any help will be greatly appreciated.
Thanks
 
A

Andy Hull

Hi Peter

The following line in your code needs square brackets around the column
named "Value" as it is a reserved word in SQL...

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration, Value, DestCode, BNumber) "
& _

....should be...

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration, [Value], DestCode, BNumber)
" & _


hth

Andy Hull
 
P

Pieter Wijnen

Right & Wrong
The place the brackets are required is in the SELECT Statement

Pieter

Andy Hull said:
Hi Peter

The following line in your code needs square brackets around the column
named "Value" as it is a reserved word in SQL...

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration, Value, DestCode, BNumber) "
& _

...should be...

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration, [Value], DestCode, BNumber)
" & _


hth

Andy Hull


Peter Clancy said:
Hi,
I need a second pair of eyes to glance over this statement because I
cannot
spot the syntax error in it.
The data is being saved into a table called Calldata which has the
following
field definitions:
ANumber Text(30)
StartDate Text(15)
StartTime Text(10)
Duration Number(Decimal)
Value Currency
DestCode Text(10)
BNumber Text(30)


Dim ANumber As String
Dim BNumber As String
Dim DestCode As String
Dim StartDate As String
Dim StartTime As String
Dim DurMins As Single
Dim Value As Currency

Dim Comma As String
Dim DblQuote As String
Dim SglQuote As String

Comma = Chr(44)
DblQuote = Chr(34)
SglQuote = Chr(39)

The data is parsed from an input file and stored in the above variables
before creating the input string as below:

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration,
Value,
DestCode, BNumber) " & _
"values(" & DblQuote & ANumber & DblQuote & Comma
& _
DblQuote & StartDate & DblQuote & Comma & _
DblQuote & StartTime & DblQuote & Comma & _
DurMins & Comma & _
SglQuote & Value & SglQuote & Comma & _
DblQuote & DestCode & DblQuote & Comma & _
DblQuote & BNumber & DblQuote & ");"

A debug.print of InsertString shows:

debug.Print InsertString
INSERT INTO Calldata ( ANumber, StartDate, StartTime, Duration, Value,
DestCode, BNumber) values("02077384851
","01-Aug-2006","08:20",50.83333,'0.506',"020867","02086786396");

I have tried the currency field both with and without the single quote
but
all I am getting is a syntax error in the insert statement and I cannot
see
where. Any help will be greatly appreciated.
Thanks
Click to expand...
Click to expand...
 
D

Douglas J. Steele

There is no SELECT statement as part of that INSERT INTO statement. The
square brackets should be placed around the field name Value as Andy
suggested.

No quotes should be used around the currency value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Pieter Wijnen"
Right & Wrong
The place the brackets are required is in the SELECT Statement

Pieter

Andy Hull said:
Hi Peter

The following line in your code needs square brackets around the column
named "Value" as it is a reserved word in SQL...

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration, Value, DestCode, BNumber)
"
& _

...should be...

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration, [Value], DestCode,
BNumber)
" & _


hth

Andy Hull


Peter Clancy said:
Hi,
I need a second pair of eyes to glance over this statement because I
cannot
spot the syntax error in it.
The data is being saved into a table called Calldata which has the
following
field definitions:
ANumber Text(30)
StartDate Text(15)
StartTime Text(10)
Duration Number(Decimal)
Value Currency
DestCode Text(10)
BNumber Text(30)


Dim ANumber As String
Dim BNumber As String
Dim DestCode As String
Dim StartDate As String
Dim StartTime As String
Dim DurMins As Single
Dim Value As Currency

Dim Comma As String
Dim DblQuote As String
Dim SglQuote As String

Comma = Chr(44)
DblQuote = Chr(34)
SglQuote = Chr(39)

The data is parsed from an input file and stored in the above variables
before creating the input string as below:

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration,
Value,
DestCode, BNumber) " & _
"values(" & DblQuote & ANumber & DblQuote &
Comma & _
DblQuote & StartDate & DblQuote & Comma & _
DblQuote & StartTime & DblQuote & Comma & _
DurMins & Comma & _
SglQuote & Value & SglQuote & Comma & _
DblQuote & DestCode & DblQuote & Comma & _
DblQuote & BNumber & DblQuote & ");"

A debug.print of InsertString shows:

debug.Print InsertString
INSERT INTO Calldata ( ANumber, StartDate, StartTime, Duration, Value,
DestCode, BNumber) values("02077384851
","01-Aug-2006","08:20",50.83333,'0.506',"020867","02086786396");

I have tried the currency field both with and without the single quote
but
all I am getting is a syntax error in the insert statement and I cannot
see
where. Any help will be greatly appreciated.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
P

Pieter Wijnen

yupp, i meant in the VALUES list

Pieter

Douglas J. Steele said:
There is no SELECT statement as part of that INSERT INTO statement. The
square brackets should be placed around the field name Value as Andy
suggested.

No quotes should be used around the currency value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Pieter Wijnen"
Right & Wrong
The place the brackets are required is in the SELECT Statement

Pieter

Andy Hull said:
Hi Peter

The following line in your code needs square brackets around the column
named "Value" as it is a reserved word in SQL...

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration, Value, DestCode, BNumber)
"
& _

...should be...

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration, [Value], DestCode,
BNumber)
" & _


hth

Andy Hull


:

Hi,
I need a second pair of eyes to glance over this statement because I
cannot
spot the syntax error in it.
The data is being saved into a table called Calldata which has the
following
field definitions:
ANumber Text(30)
StartDate Text(15)
StartTime Text(10)
Duration Number(Decimal)
Value Currency
DestCode Text(10)
BNumber Text(30)


Dim ANumber As String
Dim BNumber As String
Dim DestCode As String
Dim StartDate As String
Dim StartTime As String
Dim DurMins As Single
Dim Value As Currency

Dim Comma As String
Dim DblQuote As String
Dim SglQuote As String

Comma = Chr(44)
DblQuote = Chr(34)
SglQuote = Chr(39)

The data is parsed from an input file and stored in the above variables
before creating the input string as below:

InsertString = "INSERT INTO Calldata " & _
"( ANumber, StartDate, StartTime, Duration,
Value,
DestCode, BNumber) " & _
"values(" & DblQuote & ANumber & DblQuote &
Comma & _
DblQuote & StartDate & DblQuote & Comma & _
DblQuote & StartTime & DblQuote & Comma & _
DurMins & Comma & _
SglQuote & Value & SglQuote & Comma & _
DblQuote & DestCode & DblQuote & Comma & _
DblQuote & BNumber & DblQuote & ");"

A debug.print of InsertString shows:

debug.Print InsertString
INSERT INTO Calldata ( ANumber, StartDate, StartTime, Duration, Value,
DestCode, BNumber) values("02077384851
","01-Aug-2006","08:20",50.83333,'0.506',"020867","02086786396");

I have tried the currency field both with and without the single quote
but
all I am getting is a syntax error in the insert statement and I cannot
see
where. Any help will be greatly appreciated.
Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MSACCESS 97 APPLICATION ERROR CONVERTING FROM TEXT TO MEMO 4

Top