Can't insert record into table

D

Dou

In a table, There are Numeric, date and text fields,if I open table
using the UI and enter a new record leaving the
Numeric and date fields empty, It can insert records into a table.
But It can't insert a record into the table using INSERT INTO statement
when input data on the form and leaving the Numeric and date fields
empty. I have set numeric and date field's Required property to No.
How to solve this problem? thanks.

My table structure is:

GroupCategory text 50

EquipNo text 15

EquipDesc text 200

AssetNo text 50

PartNo text 50

ModelNo text 50

SerialNo text 50

EquipDetails Memo -

OriginalCost Single 4

Location text 50

PurchasedDate Date/Time 8

InstalledDate Date/Time 8

WarranteeUntil Date/Time 8

TechnicalFile text 100

Manufacturer text 200

OutOfService Integer 2

OutUntil Date/Time 8

TakenOut Date/Time 8

LastReadingDate Date/Time 8

CurrentHours Single 4

CurrentMiles Single 4

CurrentKM Single 4


My SQL statement is:

MySQL = "INSERT INTO [Equipment] VALUES('" & Me![cbGroupCat] & "','" &
Me![tbEquipNo] & "',""" & Me![tbEquipDesc] & """,""" & Me![tbAssetNo] &
""",""" & Me![tbPartNo] & """,""" & Me![tbModelNo] & """,""" &
Me![tbSerialNo] & """,""" & Me![tbDetails] & """," & tbOriginCost & ",'" &
tbLocation & "',#" & tbPurchasedDate & "#,#" & tbInstalledDate & "#,#" &
tbWarranteeUntil & "#,""" & tbTechFile & """,""" & tbMFR & """,1,#" &
tbOutUntil & "#,#" & tbTakenOut & "#,#" & tbLastReading & "#," &
tbCurrentHours & "," & tbCurrentMiles & "," & tbCurrentKM & ");"
 
S

SteveS

Dou,

The 'INSERT INTO' syntax is

INSERT INTO tableName (list_of_fields) VALUES (list_of_values).

Your SQL statement is missing the list of fields and some of the values
are missing the 'ME.[]' syntax.

In your case it appears that the values are from unbound controls. Given
your table structure and SQL example, this is the SQL statement I came
up with. I created the table and a form; when I ran the statement
(CurrentDb.Execute MySQL), my test (garbage) data was inserted into the
table.


(I use the following format because it is easier for me to find my errors.)

MySQL = "INSERT INTO Equipment (GroupCategory, EquipNo, EquipDesc, "
MySQL = MySQL & "AssetNo, PartNo, ModelNo, SerialNo, "
MySQL = MySQL & "EquipDetails, OriginalCost, Location, "
MySQL = MySQL & "PurchasedDate, InstalledDate, WarranteeUntil, "
MySQL = MySQL & "TechnicalFile, Manufacturer, OutOfService, "
MySQL = MySQL & "OutUntil, TakenOut, LastReadingDate, "
MySQL = MySQL & "CurrentHours, CurrentMiles, CurrentKM) "

MySQL = MySQL & "VALUES('" & Me![cbGroupCat] & "','" & Me![tbEquipNo]
MySQL = MySQL & "','" & Me![tbEquipDesc] & "','" & Me![tbAssetNo]
MySQL = MySQL & "','" & Me![tbPartNo] & "','" & Me![tbModelNo]
MySQL = MySQL & "','" & Me![tbSerialNo] & "','" & Me![tbDetails]
MySQL = MySQL & "'," & Me![tbOriginCost] & ",'" & Me![tbLocation]

'the next should be one line
MySQL = MySQL & "',#" & Me![tbPurchasedDate] & "#,#" & Me![tbInstalledDate]

MySQL = MySQL & "#,#" & Me![tbWarranteeUntil] & "#,'" & Me![tbTechFile]
MySQL = MySQL & "','" & Me![tbMFR] & "',1,#" & Me![tbOutUntil]
MySQL = MySQL & "#,#" & Me![tbTakenOut] & "#,#" & Me![tbLastReading]
MySQL = MySQL & "#," & Me![tbCurrentHours] & "," & Me![tbCurrentMiles]
MySQL = MySQL & "," & Me![tbCurrentKM] & ");"


HTH

Steve
--------------------------------
"Nunc Tutus Exitus Computarus."
(It's Now Safe To Turn Off Your Computer.)


In a table, There are Numeric, date and text fields,if I open table
using the UI and enter a new record leaving the
Numeric and date fields empty, It can insert records into a table.
But It can't insert a record into the table using INSERT INTO statement
when input data on the form and leaving the Numeric and date fields
empty. I have set numeric and date field's Required property to No.
How to solve this problem? thanks.

My table structure is:

GroupCategory text 50

EquipNo text 15

EquipDesc text 200

AssetNo text 50

PartNo text 50

ModelNo text 50

SerialNo text 50

EquipDetails Memo -

OriginalCost Single 4

Location text 50

PurchasedDate Date/Time 8

InstalledDate Date/Time 8

WarranteeUntil Date/Time 8

TechnicalFile text 100

Manufacturer text 200

OutOfService Integer 2

OutUntil Date/Time 8

TakenOut Date/Time 8

LastReadingDate Date/Time 8

CurrentHours Single 4

CurrentMiles Single 4

CurrentKM Single 4


My SQL statement is:

MySQL = "INSERT INTO [Equipment] VALUES('" & Me![cbGroupCat] & "','" &
Me![tbEquipNo] & "',""" & Me![tbEquipDesc] & """,""" & Me![tbAssetNo] &
""",""" & Me![tbPartNo] & """,""" & Me![tbModelNo] & """,""" &
Me![tbSerialNo] & """,""" & Me![tbDetails] & """," & tbOriginCost & ",'" &
tbLocation & "',#" & tbPurchasedDate & "#,#" & tbInstalledDate & "#,#" &
tbWarranteeUntil & "#,""" & tbTechFile & """,""" & tbMFR & """,1,#" &
tbOutUntil & "#,#" & tbTakenOut & "#,#" & tbLastReading & "#," &
tbCurrentHours & "," & tbCurrentMiles & "," & tbCurrentKM & ");"
Click to expand...
 
D

Dirk Goldgar

Dou said:
In a table, There are Numeric, date and text fields,if I open table
using the UI and enter a new record leaving the
Numeric and date fields empty, It can insert records into a table.
But It can't insert a record into the table using INSERT INTO
statement when input data on the form and leaving the Numeric and
date fields empty. I have set numeric and date field's Required
property to No.
How to solve this problem? thanks.

My table structure is:

GroupCategory text 50

EquipNo text 15

EquipDesc text 200

AssetNo text 50

PartNo text 50

ModelNo text 50

SerialNo text 50

EquipDetails Memo -

OriginalCost Single 4

Location text 50

PurchasedDate Date/Time 8

InstalledDate Date/Time 8

WarranteeUntil Date/Time 8

TechnicalFile text 100

Manufacturer text 200

OutOfService Integer 2

OutUntil Date/Time 8

TakenOut Date/Time 8

LastReadingDate Date/Time 8

CurrentHours Single 4

CurrentMiles Single 4

CurrentKM Single 4


My SQL statement is:

MySQL = "INSERT INTO [Equipment] VALUES('" & Me![cbGroupCat] & "','" &
Me![tbEquipNo] & "',""" & Me![tbEquipDesc] & """,""" & Me![tbAssetNo]
& """,""" & Me![tbPartNo] & """,""" & Me![tbModelNo] & """,""" &
Me![tbSerialNo] & """,""" & Me![tbDetails] & """," & tbOriginCost &
",'" & tbLocation & "',#" & tbPurchasedDate & "#,#" & tbInstalledDate
& "#,#" & tbWarranteeUntil & "#,""" & tbTechFile & """,""" & tbMFR &
""",1,#" & tbOutUntil & "#,#" & tbTakenOut & "#,#" & tbLastReading &
"#," & tbCurrentHours & "," & tbCurrentMiles & "," & tbCurrentKM &
");"
Click to expand...

I'd guess your problem comes because, when the numeric and date fields
are Null, you don't end up with a valid SQL string. Use the Nz()
function for each of these fields to supply the string "Null" if the
field is Null. Here's an example:

... & Nz(tbOriginCost, "Null") & ...

You may also want to do this with your text and memo fields, unless you
really want to insert zero-length strings ('') in your table instead of
Null values. Be aware that there is a big difference, in database
terms, between a field whose value is '' (i.e., a zero-length string)
and a field whose value is Null.
 
S

SteveS

Dou,
I just read Dirk's post; I didn't check to see what would happen if not
all data was entered. I left a couple of the boxes empty and of course
it bombed. In my defense, it was early in the morning.....

An alternative to adding NZ() to the SQL statement, since it is only a
text string, is to build the INSERT statement 'on the fly'.

Here it is:

Dim MySQL As String
Dim SQLFields As String
Dim SQLValues As String

SQLFields = "("
SQLValues = "VALUES("
If Not IsNull(Me![cbGroupCat]) Then
SQLFields = SQLFields & "GroupCategory, "
SQLValues = SQLValues & "'" & Me![cbGroupCat] & "', "
End If

If Not IsNull(Me![tbEquipNo]) Then
SQLFields = SQLFields & "EquipNo, "
SQLValues = SQLValues & "'" & Me![tbEquipNo] & "', "
End If

If Not IsNull(Me![tbEquipDesc]) Then
SQLFields = SQLFields & "EquipDesc, "
SQLValues = SQLValues & "'" & Me![tbEquipDesc] & "', "
End If

If Not IsNull(Me![tbAssetNo]) Then
SQLFields = SQLFields & "AssetNo, "
SQLValues = SQLValues & "'" & Me![tbAssetNo] & "', "
End If

If Not IsNull(Me![tbPartNo]) Then
SQLFields = SQLFields & "PartNo, "
SQLValues = SQLValues & "'" & Me![tbPartNo] & "', "
End If

If Not IsNull(Me![tbModelNo]) Then
SQLFields = SQLFields & "ModelNo, "
SQLValues = SQLValues & "'" & Me![tbModelNo] & "', "
End If

If Not IsNull(Me![tbSerialNo]) Then
SQLFields = SQLFields & "SerialNo, "
SQLValues = SQLValues & "'" & Me![tbSerialNo] & "', "
End If

If Not IsNull(Me![tbDetails]) Then
SQLFields = SQLFields & "EquipDetails, "
SQLValues = SQLValues & "'" & Me![tbDetails] & "', "
End If

If Not IsNull(Me![tbOriginCost]) Then
SQLFields = SQLFields & "OriginalCost, "
SQLValues = SQLValues & Me![tbOriginCost] & ", "
End If

If Not IsNull(Me![tbLocation]) Then
SQLFields = SQLFields & "Location, "
SQLValues = SQLValues & "'" & Me![tbLocation] & "', "
End If

If Not IsNull(Me![tbPurchasedDate]) Then
SQLFields = SQLFields & "PurchasedDate, "
SQLValues = SQLValues & "#" & Me![tbPurchasedDate] & "#, "
End If

If Not IsNull(Me![tbInstalledDate]) Then
SQLFields = SQLFields & "InstalledDate, "
SQLValues = SQLValues & "#" & Me![tbInstalledDate] & "#, "
End If

If Not IsNull(Me![tbWarranteeUntil]) Then
SQLFields = SQLFields & "WarranteeUntil, "
SQLValues = SQLValues & "#" & Me![tbWarranteeUntil] & "#, "
End If

If Not IsNull(Me![tbTechFile]) Then
SQLFields = SQLFields & "TechnicalFile, "
SQLValues = SQLValues & "'" & Me![tbTechFile] & "', "
End If

If Not IsNull(Me![tbMFR]) Then
SQLFields = SQLFields & "Manufacturer, "
SQLValues = SQLValues & "'" & Me![tbMFR] & "', "
End If

'this field you hard coded to = 1
'If Not IsNull(Me![tblOutOfService]) Then
SQLFields = SQLFields & "OutOfService, "
SQLValues = SQLValues & 1 & ", "
'SQLValues = SQLValues & Me![tblOutOfService] & ", "
'End If

If Not IsNull(Me![tbOutUntil]) Then
SQLFields = SQLFields & "OutUntil, "
SQLValues = SQLValues & "#" & Me![tbOutUntil] & "#, "
End If

If Not IsNull(Me![tbTakenOut]) Then
SQLFields = SQLFields & "TakenOut, "
SQLValues = SQLValues & "#" & Me![tbTakenOut] & "#, "
End If

If Not IsNull(Me![tbLastReading]) Then
SQLFields = SQLFields & "LastReadingDate, "
SQLValues = SQLValues & "#" & Me![tbLastReading] & "#, "
End If

If Not IsNull(Me![tbCurrentHours]) Then
SQLFields = SQLFields & "CurrentHours, "
SQLValues = SQLValues & Me![tbCurrentHours] & ", "
End If

If Not IsNull(Me![tbCurrentMiles]) Then
SQLFields = SQLFields & "CurrentMiles, "
SQLValues = SQLValues & Me![tbCurrentMiles] & ", "
End If

If Not IsNull(Me![tbCurrentKM]) Then
SQLFields = SQLFields & "CurrentKM, "
SQLValues = SQLValues & Me![tbCurrentKM] & ", "
End If

SQLFields = Left(SQLFields, Len(SQLFields) - 2) & ")"
SQLValues = Left(SQLValues, Len(SQLValues) - 2) & ");"

MySQL = "INSERT INTO Equipment " & SQLFields & " " & SQLValues
'MsgBox MySQL




Steve
 
D

Dou

The PK is EquipNo. When I left Numeric and date fields empty. The error
message is

INSERT INTO statement syntax error

If I don't left them empty, then It can insert record into the table. Why?

I add NZ function into my SQL string, the error message is the same.



SteveS said:
Dou,
I just read Dirk's post; I didn't check to see what would happen if not
all data was entered. I left a couple of the boxes empty and of course
it bombed. In my defense, it was early in the morning.....

An alternative to adding NZ() to the SQL statement, since it is only a
text string, is to build the INSERT statement 'on the fly'.

Here it is:

Dim MySQL As String
Dim SQLFields As String
Dim SQLValues As String

SQLFields = "("
SQLValues = "VALUES("
If Not IsNull(Me![cbGroupCat]) Then
SQLFields = SQLFields & "GroupCategory, "
SQLValues = SQLValues & "'" & Me![cbGroupCat] & "', "
End If

If Not IsNull(Me![tbEquipNo]) Then
SQLFields = SQLFields & "EquipNo, "
SQLValues = SQLValues & "'" & Me![tbEquipNo] & "', "
End If

If Not IsNull(Me![tbEquipDesc]) Then
SQLFields = SQLFields & "EquipDesc, "
SQLValues = SQLValues & "'" & Me![tbEquipDesc] & "', "
End If

If Not IsNull(Me![tbAssetNo]) Then
SQLFields = SQLFields & "AssetNo, "
SQLValues = SQLValues & "'" & Me![tbAssetNo] & "', "
End If

If Not IsNull(Me![tbPartNo]) Then
SQLFields = SQLFields & "PartNo, "
SQLValues = SQLValues & "'" & Me![tbPartNo] & "', "
End If

If Not IsNull(Me![tbModelNo]) Then
SQLFields = SQLFields & "ModelNo, "
SQLValues = SQLValues & "'" & Me![tbModelNo] & "', "
End If

If Not IsNull(Me![tbSerialNo]) Then
SQLFields = SQLFields & "SerialNo, "
SQLValues = SQLValues & "'" & Me![tbSerialNo] & "', "
End If

If Not IsNull(Me![tbDetails]) Then
SQLFields = SQLFields & "EquipDetails, "
SQLValues = SQLValues & "'" & Me![tbDetails] & "', "
End If

If Not IsNull(Me![tbOriginCost]) Then
SQLFields = SQLFields & "OriginalCost, "
SQLValues = SQLValues & Me![tbOriginCost] & ", "
End If

If Not IsNull(Me![tbLocation]) Then
SQLFields = SQLFields & "Location, "
SQLValues = SQLValues & "'" & Me![tbLocation] & "', "
End If

If Not IsNull(Me![tbPurchasedDate]) Then
SQLFields = SQLFields & "PurchasedDate, "
SQLValues = SQLValues & "#" & Me![tbPurchasedDate] & "#, "
End If

If Not IsNull(Me![tbInstalledDate]) Then
SQLFields = SQLFields & "InstalledDate, "
SQLValues = SQLValues & "#" & Me![tbInstalledDate] & "#, "
End If

If Not IsNull(Me![tbWarranteeUntil]) Then
SQLFields = SQLFields & "WarranteeUntil, "
SQLValues = SQLValues & "#" & Me![tbWarranteeUntil] & "#, "
End If

If Not IsNull(Me![tbTechFile]) Then
SQLFields = SQLFields & "TechnicalFile, "
SQLValues = SQLValues & "'" & Me![tbTechFile] & "', "
End If

If Not IsNull(Me![tbMFR]) Then
SQLFields = SQLFields & "Manufacturer, "
SQLValues = SQLValues & "'" & Me![tbMFR] & "', "
End If

'this field you hard coded to = 1
'If Not IsNull(Me![tblOutOfService]) Then
SQLFields = SQLFields & "OutOfService, "
SQLValues = SQLValues & 1 & ", "
'SQLValues = SQLValues & Me![tblOutOfService] & ", "
'End If

If Not IsNull(Me![tbOutUntil]) Then
SQLFields = SQLFields & "OutUntil, "
SQLValues = SQLValues & "#" & Me![tbOutUntil] & "#, "
End If

If Not IsNull(Me![tbTakenOut]) Then
SQLFields = SQLFields & "TakenOut, "
SQLValues = SQLValues & "#" & Me![tbTakenOut] & "#, "
End If

If Not IsNull(Me![tbLastReading]) Then
SQLFields = SQLFields & "LastReadingDate, "
SQLValues = SQLValues & "#" & Me![tbLastReading] & "#, "
End If

If Not IsNull(Me![tbCurrentHours]) Then
SQLFields = SQLFields & "CurrentHours, "
SQLValues = SQLValues & Me![tbCurrentHours] & ", "
End If

If Not IsNull(Me![tbCurrentMiles]) Then
SQLFields = SQLFields & "CurrentMiles, "
SQLValues = SQLValues & Me![tbCurrentMiles] & ", "
End If

If Not IsNull(Me![tbCurrentKM]) Then
SQLFields = SQLFields & "CurrentKM, "
SQLValues = SQLValues & Me![tbCurrentKM] & ", "
End If

SQLFields = Left(SQLFields, Len(SQLFields) - 2) & ")"
SQLValues = Left(SQLValues, Len(SQLValues) - 2) & ");"

MySQL = "INSERT INTO Equipment " & SQLFields & " " & SQLValues
'MsgBox MySQL




Steve
Click to expand...
 
S

SteveS

Please post the code you are using now.

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
The PK is EquipNo. When I left Numeric and date fields empty. The error
message is

INSERT INTO statement syntax error

If I don't left them empty, then It can insert record into the table. Why?

I add NZ function into my SQL string, the error message is the same.



Dou,
I just read Dirk's post; I didn't check to see what would happen if not
all data was entered. I left a couple of the boxes empty and of course
it bombed. In my defense, it was early in the morning.....

An alternative to adding NZ() to the SQL statement, since it is only a
text string, is to build the INSERT statement 'on the fly'.

Here it is:

Dim MySQL As String
Dim SQLFields As String
Dim SQLValues As String

SQLFields = "("
SQLValues = "VALUES("
If Not IsNull(Me![cbGroupCat]) Then
SQLFields = SQLFields & "GroupCategory, "
SQLValues = SQLValues & "'" & Me![cbGroupCat] & "', "
End If

If Not IsNull(Me![tbEquipNo]) Then
SQLFields = SQLFields & "EquipNo, "
SQLValues = SQLValues & "'" & Me![tbEquipNo] & "', "
End If

If Not IsNull(Me![tbEquipDesc]) Then
SQLFields = SQLFields & "EquipDesc, "
SQLValues = SQLValues & "'" & Me![tbEquipDesc] & "', "
End If

If Not IsNull(Me![tbAssetNo]) Then
SQLFields = SQLFields & "AssetNo, "
SQLValues = SQLValues & "'" & Me![tbAssetNo] & "', "
End If

If Not IsNull(Me![tbPartNo]) Then
SQLFields = SQLFields & "PartNo, "
SQLValues = SQLValues & "'" & Me![tbPartNo] & "', "
End If

If Not IsNull(Me![tbModelNo]) Then
SQLFields = SQLFields & "ModelNo, "
SQLValues = SQLValues & "'" & Me![tbModelNo] & "', "
End If

If Not IsNull(Me![tbSerialNo]) Then
SQLFields = SQLFields & "SerialNo, "
SQLValues = SQLValues & "'" & Me![tbSerialNo] & "', "
End If

If Not IsNull(Me![tbDetails]) Then
SQLFields = SQLFields & "EquipDetails, "
SQLValues = SQLValues & "'" & Me![tbDetails] & "', "
End If

If Not IsNull(Me![tbOriginCost]) Then
SQLFields = SQLFields & "OriginalCost, "
SQLValues = SQLValues & Me![tbOriginCost] & ", "
End If

If Not IsNull(Me![tbLocation]) Then
SQLFields = SQLFields & "Location, "
SQLValues = SQLValues & "'" & Me![tbLocation] & "', "
End If

If Not IsNull(Me![tbPurchasedDate]) Then
SQLFields = SQLFields & "PurchasedDate, "
SQLValues = SQLValues & "#" & Me![tbPurchasedDate] & "#, "
End If

If Not IsNull(Me![tbInstalledDate]) Then
SQLFields = SQLFields & "InstalledDate, "
SQLValues = SQLValues & "#" & Me![tbInstalledDate] & "#, "
End If

If Not IsNull(Me![tbWarranteeUntil]) Then
SQLFields = SQLFields & "WarranteeUntil, "
SQLValues = SQLValues & "#" & Me![tbWarranteeUntil] & "#, "
End If

If Not IsNull(Me![tbTechFile]) Then
SQLFields = SQLFields & "TechnicalFile, "
SQLValues = SQLValues & "'" & Me![tbTechFile] & "', "
End If

If Not IsNull(Me![tbMFR]) Then
SQLFields = SQLFields & "Manufacturer, "
SQLValues = SQLValues & "'" & Me![tbMFR] & "', "
End If

'this field you hard coded to = 1
'If Not IsNull(Me![tblOutOfService]) Then
SQLFields = SQLFields & "OutOfService, "
SQLValues = SQLValues & 1 & ", "
'SQLValues = SQLValues & Me![tblOutOfService] & ", "
'End If

If Not IsNull(Me![tbOutUntil]) Then
SQLFields = SQLFields & "OutUntil, "
SQLValues = SQLValues & "#" & Me![tbOutUntil] & "#, "
End If

If Not IsNull(Me![tbTakenOut]) Then
SQLFields = SQLFields & "TakenOut, "
SQLValues = SQLValues & "#" & Me![tbTakenOut] & "#, "
End If

If Not IsNull(Me![tbLastReading]) Then
SQLFields = SQLFields & "LastReadingDate, "
SQLValues = SQLValues & "#" & Me![tbLastReading] & "#, "
End If

If Not IsNull(Me![tbCurrentHours]) Then
SQLFields = SQLFields & "CurrentHours, "
SQLValues = SQLValues & Me![tbCurrentHours] & ", "
End If

If Not IsNull(Me![tbCurrentMiles]) Then
SQLFields = SQLFields & "CurrentMiles, "
SQLValues = SQLValues & Me![tbCurrentMiles] & ", "
End If

If Not IsNull(Me![tbCurrentKM]) Then
SQLFields = SQLFields & "CurrentKM, "
SQLValues = SQLValues & Me![tbCurrentKM] & ", "
End If

SQLFields = Left(SQLFields, Len(SQLFields) - 2) & ")"
SQLValues = Left(SQLValues, Len(SQLValues) - 2) & ");"

MySQL = "INSERT INTO Equipment " & SQLFields & " " & SQLValues
'MsgBox MySQL




Steve
Click to expand...
Click to expand...
 
D

Dou

The following two SQL's error message is the same.
1.

MySQL = "INSERT INTO [Equipment] VALUES('" & Nz(cbGroupCat) & "','" &
Nz(tbEquipNo) & "',""" & Nz(tbEquipDesc) & """,""" & Nz(tbAssetNo) & ""","""
& Nz(tbPartNo) & """,""" & Nz(tbModelNo) & """,""" & Nz(tbSerialNo) &
""",""" & Nz(tbDetails) & """," & Nz(tbOriginCost) & ",'" & Nz(tbLocation) &
"',#" & Nz(tbPurchasedDate) & "#,#" & Nz(tbInstalledDate) & "#,#" &
Nz(tbWarranteeUntil) & "#,""" & Nz(tbTechFile) & """,""" & Nz(tbMFR) &
""",1,#" & Nz(tbOutUntil) & "#,#" & Nz(tbTakenOut) & "#,#" &
Nz(tbLastReading) & "#," & Nz(tbCurrentHours) & "," & Nz(tbCurrentMiles) &
"," & Nz(tbCurrentKM) & ");"

2. Dim MySQL As String
Dim SQLFields As String
Dim SQLValues As String

SQLFields = "("
SQLValues = "VALUES("
If Not IsNull(Me![cbGroupCat]) Then
SQLFields = SQLFields & "GroupCategory, "
SQLValues = SQLValues & "'" & Me![cbGroupCat] & "', "
End If

If Not IsNull(Me![tbEquipNo]) Then
SQLFields = SQLFields & "EquipNo, "
SQLValues = SQLValues & "'" & Me![tbEquipNo] & "', "
End If

If Not IsNull(Me![tbEquipDesc]) Then
SQLFields = SQLFields & "EquipDesc, "
SQLValues = SQLValues & "'" & Me![tbEquipDesc] & "', "
End If

If Not IsNull(Me![tbAssetNo]) Then
SQLFields = SQLFields & "AssetNo, "
SQLValues = SQLValues & "'" & Me![tbAssetNo] & "', "
End If

If Not IsNull(Me![tbPartNo]) Then
SQLFields = SQLFields & "PartNo, "
SQLValues = SQLValues & "'" & Me![tbPartNo] & "', "
End If

If Not IsNull(Me![tbModelNo]) Then
SQLFields = SQLFields & "ModelNo, "
SQLValues = SQLValues & "'" & Me![tbModelNo] & "', "
End If

If Not IsNull(Me![tbSerialNo]) Then
SQLFields = SQLFields & "SerialNo, "
SQLValues = SQLValues & "'" & Me![tbSerialNo] & "', "
End If

If Not IsNull(Me![tbDetails]) Then
SQLFields = SQLFields & "EquipDetails, "
SQLValues = SQLValues & "'" & Me![tbDetails] & "', "
End If

If Not IsNull(Me![tbOriginCost]) Then
SQLFields = SQLFields & "OriginalCost, "
SQLValues = SQLValues & Me![tbOriginCost] & ", "
End If

If Not IsNull(Me![tbLocation]) Then
SQLFields = SQLFields & "Location, "
SQLValues = SQLValues & "'" & Me![tbLocation] & "', "
End If

If Not IsNull(Me![tbPurchasedDate]) Then
SQLFields = SQLFields & "PurchasedDate, "
SQLValues = SQLValues & "#" & Me![tbPurchasedDate] & "#, "
End If

If Not IsNull(Me![tbInstalledDate]) Then
SQLFields = SQLFields & "InstalledDate, "
SQLValues = SQLValues & "#" & Me![tbInstalledDate] & "#, "
End If

If Not IsNull(Me![tbWarranteeUntil]) Then
SQLFields = SQLFields & "WarranteeUntil, "
SQLValues = SQLValues & "#" & Me![tbWarranteeUntil] & "#, "
End If

If Not IsNull(Me![tbTechFile]) Then
SQLFields = SQLFields & "TechnicalFile, "
SQLValues = SQLValues & "'" & Me![tbTechFile] & "', "
End If

If Not IsNull(Me![tbMFR]) Then
SQLFields = SQLFields & "Manufacturer, "
SQLValues = SQLValues & "'" & Me![tbMFR] & "', "
End If

'this field you hard coded to = 1
'If Not IsNull(Me![tblOutOfService]) Then
SQLFields = SQLFields & "OutOfService, "
SQLValues = SQLValues & 1 & ", "
'SQLValues = SQLValues & Me![tblOutOfService] & ", "
'End If

If Not IsNull(Me![tbOutUntil]) Then
SQLFields = SQLFields & "OutUntil, "
SQLValues = SQLValues & "#" & Me![tbOutUntil] & "#, "
End If

If Not IsNull(Me![tbTakenOut]) Then
SQLFields = SQLFields & "TakenOut, "
SQLValues = SQLValues & "#" & Me![tbTakenOut] & "#, "
End If

If Not IsNull(Me![tbLastReading]) Then
SQLFields = SQLFields & "LastReadingDate, "
SQLValues = SQLValues & "#" & Me![tbLastReading] & "#, "
End If

If Not IsNull(Me![tbCurrentHours]) Then
SQLFields = SQLFields & "CurrentHours, "
SQLValues = SQLValues & Me![tbCurrentHours] & ", "
End If

If Not IsNull(Me![tbCurrentMiles]) Then
SQLFields = SQLFields & "CurrentMiles, "
SQLValues = SQLValues & Me![tbCurrentMiles] & ", "
End If

If Not IsNull(Me![tbCurrentKM]) Then
SQLFields = SQLFields & "CurrentKM, "
SQLValues = SQLValues & Me![tbCurrentKM] & ", "
End If

SQLFields = Left(SQLFields, Len(SQLFields) - 2) & ")"
SQLValues = Left(SQLValues, Len(SQLValues) - 2) & ");"

MySQL = "INSERT INTO Equipment " & SQLFields & " " & SQLValues


SteveS said:
Please post the code you are using now.

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
The PK is EquipNo. When I left Numeric and date fields empty. The error
message is

INSERT INTO statement syntax error

If I don't left them empty, then It can insert record into the table. Why?

I add NZ function into my SQL string, the error message is the same.



Dou,
I just read Dirk's post; I didn't check to see what would happen if not
all data was entered. I left a couple of the boxes empty and of course
it bombed. In my defense, it was early in the morning.....

An alternative to adding NZ() to the SQL statement, since it is only a
text string, is to build the INSERT statement 'on the fly'.

Here it is:

Dim MySQL As String
Dim SQLFields As String
Dim SQLValues As String

SQLFields = "("
SQLValues = "VALUES("
If Not IsNull(Me![cbGroupCat]) Then
SQLFields = SQLFields & "GroupCategory, "
SQLValues = SQLValues & "'" & Me![cbGroupCat] & "', "
End If

If Not IsNull(Me![tbEquipNo]) Then
SQLFields = SQLFields & "EquipNo, "
SQLValues = SQLValues & "'" & Me![tbEquipNo] & "', "
End If

If Not IsNull(Me![tbEquipDesc]) Then
SQLFields = SQLFields & "EquipDesc, "
SQLValues = SQLValues & "'" & Me![tbEquipDesc] & "', "
End If

If Not IsNull(Me![tbAssetNo]) Then
SQLFields = SQLFields & "AssetNo, "
SQLValues = SQLValues & "'" & Me![tbAssetNo] & "', "
End If

If Not IsNull(Me![tbPartNo]) Then
SQLFields = SQLFields & "PartNo, "
SQLValues = SQLValues & "'" & Me![tbPartNo] & "', "
End If

If Not IsNull(Me![tbModelNo]) Then
SQLFields = SQLFields & "ModelNo, "
SQLValues = SQLValues & "'" & Me![tbModelNo] & "', "
End If

If Not IsNull(Me![tbSerialNo]) Then
SQLFields = SQLFields & "SerialNo, "
SQLValues = SQLValues & "'" & Me![tbSerialNo] & "', "
End If

If Not IsNull(Me![tbDetails]) Then
SQLFields = SQLFields & "EquipDetails, "
SQLValues = SQLValues & "'" & Me![tbDetails] & "', "
End If

If Not IsNull(Me![tbOriginCost]) Then
SQLFields = SQLFields & "OriginalCost, "
SQLValues = SQLValues & Me![tbOriginCost] & ", "
End If

If Not IsNull(Me![tbLocation]) Then
SQLFields = SQLFields & "Location, "
SQLValues = SQLValues & "'" & Me![tbLocation] & "', "
End If

If Not IsNull(Me![tbPurchasedDate]) Then
SQLFields = SQLFields & "PurchasedDate, "
SQLValues = SQLValues & "#" & Me![tbPurchasedDate] & "#, "
End If

If Not IsNull(Me![tbInstalledDate]) Then
SQLFields = SQLFields & "InstalledDate, "
SQLValues = SQLValues & "#" & Me![tbInstalledDate] & "#, "
End If

If Not IsNull(Me![tbWarranteeUntil]) Then
SQLFields = SQLFields & "WarranteeUntil, "
SQLValues = SQLValues & "#" & Me![tbWarranteeUntil] & "#, "
End If

If Not IsNull(Me![tbTechFile]) Then
SQLFields = SQLFields & "TechnicalFile, "
SQLValues = SQLValues & "'" & Me![tbTechFile] & "', "
End If

If Not IsNull(Me![tbMFR]) Then
SQLFields = SQLFields & "Manufacturer, "
SQLValues = SQLValues & "'" & Me![tbMFR] & "', "
End If

'this field you hard coded to = 1
'If Not IsNull(Me![tblOutOfService]) Then
SQLFields = SQLFields & "OutOfService, "
SQLValues = SQLValues & 1 & ", "
'SQLValues = SQLValues & Me![tblOutOfService] & ", "
'End If

If Not IsNull(Me![tbOutUntil]) Then
SQLFields = SQLFields & "OutUntil, "
SQLValues = SQLValues & "#" & Me![tbOutUntil] & "#, "
End If

If Not IsNull(Me![tbTakenOut]) Then
SQLFields = SQLFields & "TakenOut, "
SQLValues = SQLValues & "#" & Me![tbTakenOut] & "#, "
End If

If Not IsNull(Me![tbLastReading]) Then
SQLFields = SQLFields & "LastReadingDate, "
SQLValues = SQLValues & "#" & Me![tbLastReading] & "#, "
End If

If Not IsNull(Me![tbCurrentHours]) Then
SQLFields = SQLFields & "CurrentHours, "
SQLValues = SQLValues & Me![tbCurrentHours] & ", "
End If

If Not IsNull(Me![tbCurrentMiles]) Then
SQLFields = SQLFields & "CurrentMiles, "
SQLValues = SQLValues & Me![tbCurrentMiles] & ", "
End If

If Not IsNull(Me![tbCurrentKM]) Then
SQLFields = SQLFields & "CurrentKM, "
SQLValues = SQLValues & Me![tbCurrentKM] & ", "
End If

SQLFields = Left(SQLFields, Len(SQLFields) - 2) & ")"
SQLValues = Left(SQLValues, Len(SQLValues) - 2) & ");"

MySQL = "INSERT INTO Equipment " & SQLFields & " " & SQLValues
'MsgBox MySQL




Steve
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

I pasted both 1 & 2 into the mdb I made up. #1 gives me the error
message but #2 doesn't.

Try adding

MsgBox MySQL

after

MySQL = statement in #1

and after

MySQL = "INSERT INTO Equipment " & SQLFields & " " & SQLValues

in #2.


What do you see? There should not be and "" or ## in the message box.


In #2 there should be the same number of field names as values.


The error message usually means that some of the values are missing or
there are one or more spelling errors, either the field names in the
code are spelled wrong or a control name is spelled wrong.

If you can't find where the error is, send me the empty, compacted,
zipped mdb and I'll see if I can find it.




The following two SQL's error message is the same.
1.

MySQL = "INSERT INTO [Equipment] VALUES('" & Nz(cbGroupCat) & "','" &
Nz(tbEquipNo) & "',""" & Nz(tbEquipDesc) & """,""" & Nz(tbAssetNo) & ""","""
& Nz(tbPartNo) & """,""" & Nz(tbModelNo) & """,""" & Nz(tbSerialNo) &
""",""" & Nz(tbDetails) & """," & Nz(tbOriginCost) & ",'" & Nz(tbLocation) &
"',#" & Nz(tbPurchasedDate) & "#,#" & Nz(tbInstalledDate) & "#,#" &
Nz(tbWarranteeUntil) & "#,""" & Nz(tbTechFile) & """,""" & Nz(tbMFR) &
""",1,#" & Nz(tbOutUntil) & "#,#" & Nz(tbTakenOut) & "#,#" &
Nz(tbLastReading) & "#," & Nz(tbCurrentHours) & "," & Nz(tbCurrentMiles) &
"," & Nz(tbCurrentKM) & ");"

2. Dim MySQL As String
Dim SQLFields As String
Dim SQLValues As String

SQLFields = "("
SQLValues = "VALUES("
If Not IsNull(Me![cbGroupCat]) Then
SQLFields = SQLFields & "GroupCategory, "
SQLValues = SQLValues & "'" & Me![cbGroupCat] & "', "
End If

If Not IsNull(Me![tbEquipNo]) Then
SQLFields = SQLFields & "EquipNo, "
SQLValues = SQLValues & "'" & Me![tbEquipNo] & "', "
End If

If Not IsNull(Me![tbEquipDesc]) Then
SQLFields = SQLFields & "EquipDesc, "
SQLValues = SQLValues & "'" & Me![tbEquipDesc] & "', "
End If

If Not IsNull(Me![tbAssetNo]) Then
SQLFields = SQLFields & "AssetNo, "
SQLValues = SQLValues & "'" & Me![tbAssetNo] & "', "
End If

If Not IsNull(Me![tbPartNo]) Then
SQLFields = SQLFields & "PartNo, "
SQLValues = SQLValues & "'" & Me![tbPartNo] & "', "
End If

If Not IsNull(Me![tbModelNo]) Then
SQLFields = SQLFields & "ModelNo, "
SQLValues = SQLValues & "'" & Me![tbModelNo] & "', "
End If

If Not IsNull(Me![tbSerialNo]) Then
SQLFields = SQLFields & "SerialNo, "
SQLValues = SQLValues & "'" & Me![tbSerialNo] & "', "
End If

If Not IsNull(Me![tbDetails]) Then
SQLFields = SQLFields & "EquipDetails, "
SQLValues = SQLValues & "'" & Me![tbDetails] & "', "
End If

If Not IsNull(Me![tbOriginCost]) Then
SQLFields = SQLFields & "OriginalCost, "
SQLValues = SQLValues & Me![tbOriginCost] & ", "
End If

If Not IsNull(Me![tbLocation]) Then
SQLFields = SQLFields & "Location, "
SQLValues = SQLValues & "'" & Me![tbLocation] & "', "
End If

If Not IsNull(Me![tbPurchasedDate]) Then
SQLFields = SQLFields & "PurchasedDate, "
SQLValues = SQLValues & "#" & Me![tbPurchasedDate] & "#, "
End If

If Not IsNull(Me![tbInstalledDate]) Then
SQLFields = SQLFields & "InstalledDate, "
SQLValues = SQLValues & "#" & Me![tbInstalledDate] & "#, "
End If

If Not IsNull(Me![tbWarranteeUntil]) Then
SQLFields = SQLFields & "WarranteeUntil, "
SQLValues = SQLValues & "#" & Me![tbWarranteeUntil] & "#, "
End If

If Not IsNull(Me![tbTechFile]) Then
SQLFields = SQLFields & "TechnicalFile, "
SQLValues = SQLValues & "'" & Me![tbTechFile] & "', "
End If

If Not IsNull(Me![tbMFR]) Then
SQLFields = SQLFields & "Manufacturer, "
SQLValues = SQLValues & "'" & Me![tbMFR] & "', "
End If

'this field you hard coded to = 1
'If Not IsNull(Me![tblOutOfService]) Then
SQLFields = SQLFields & "OutOfService, "
SQLValues = SQLValues & 1 & ", "
'SQLValues = SQLValues & Me![tblOutOfService] & ", "
'End If

If Not IsNull(Me![tbOutUntil]) Then
SQLFields = SQLFields & "OutUntil, "
SQLValues = SQLValues & "#" & Me![tbOutUntil] & "#, "
End If

If Not IsNull(Me![tbTakenOut]) Then
SQLFields = SQLFields & "TakenOut, "
SQLValues = SQLValues & "#" & Me![tbTakenOut] & "#, "
End If

If Not IsNull(Me![tbLastReading]) Then
SQLFields = SQLFields & "LastReadingDate, "
SQLValues = SQLValues & "#" & Me![tbLastReading] & "#, "
End If

If Not IsNull(Me![tbCurrentHours]) Then
SQLFields = SQLFields & "CurrentHours, "
SQLValues = SQLValues & Me![tbCurrentHours] & ", "
End If

If Not IsNull(Me![tbCurrentMiles]) Then
SQLFields = SQLFields & "CurrentMiles, "
SQLValues = SQLValues & Me![tbCurrentMiles] & ", "
End If

If Not IsNull(Me![tbCurrentKM]) Then
SQLFields = SQLFields & "CurrentKM, "
SQLValues = SQLValues & Me![tbCurrentKM] & ", "
End If

SQLFields = Left(SQLFields, Len(SQLFields) - 2) & ")"
SQLValues = Left(SQLValues, Len(SQLValues) - 2) & ");"

MySQL = "INSERT INTO Equipment " & SQLFields & " " & SQLValues


Please post the code you are using now.

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
The PK is EquipNo. When I left Numeric and date fields empty. The error
message is

INSERT INTO statement syntax error

If I don't left them empty, then It can insert record into the table.
Click to expand...
Why?
I add NZ function into my SQL string, the error message is the same.



"SteveS" <sanfu@_KILLS-SPAM_techie.com> ????


Dou,
I just read Dirk's post; I didn't check to see what would happen if not
all data was entered. I left a couple of the boxes empty and of course
it bombed. In my defense, it was early in the morning.....

An alternative to adding NZ() to the SQL statement, since it is only a
text string, is to build the INSERT statement 'on the fly'.

Here it is:

Dim MySQL As String
Dim SQLFields As String
Dim SQLValues As String

SQLFields = "("
SQLValues = "VALUES("
If Not IsNull(Me![cbGroupCat]) Then
SQLFields = SQLFields & "GroupCategory, "
SQLValues = SQLValues & "'" & Me![cbGroupCat] & "', "
End If

If Not IsNull(Me![tbEquipNo]) Then
SQLFields = SQLFields & "EquipNo, "
SQLValues = SQLValues & "'" & Me![tbEquipNo] & "', "
End If

If Not IsNull(Me![tbEquipDesc]) Then
SQLFields = SQLFields & "EquipDesc, "
SQLValues = SQLValues & "'" & Me![tbEquipDesc] & "', "
End If

If Not IsNull(Me![tbAssetNo]) Then
SQLFields = SQLFields & "AssetNo, "
SQLValues = SQLValues & "'" & Me![tbAssetNo] & "', "
End If

If Not IsNull(Me![tbPartNo]) Then
SQLFields = SQLFields & "PartNo, "
SQLValues = SQLValues & "'" & Me![tbPartNo] & "', "
End If

If Not IsNull(Me![tbModelNo]) Then
SQLFields = SQLFields & "ModelNo, "
SQLValues = SQLValues & "'" & Me![tbModelNo] & "', "
End If

If Not IsNull(Me![tbSerialNo]) Then
SQLFields = SQLFields & "SerialNo, "
SQLValues = SQLValues & "'" & Me![tbSerialNo] & "', "
End If

If Not IsNull(Me![tbDetails]) Then
SQLFields = SQLFields & "EquipDetails, "
SQLValues = SQLValues & "'" & Me![tbDetails] & "', "
End If

If Not IsNull(Me![tbOriginCost]) Then
SQLFields = SQLFields & "OriginalCost, "
SQLValues = SQLValues & Me![tbOriginCost] & ", "
End If

If Not IsNull(Me![tbLocation]) Then
SQLFields = SQLFields & "Location, "
SQLValues = SQLValues & "'" & Me![tbLocation] & "', "
End If

If Not IsNull(Me![tbPurchasedDate]) Then
SQLFields = SQLFields & "PurchasedDate, "
SQLValues = SQLValues & "#" & Me![tbPurchasedDate] & "#, "
End If

If Not IsNull(Me![tbInstalledDate]) Then
SQLFields = SQLFields & "InstalledDate, "
SQLValues = SQLValues & "#" & Me![tbInstalledDate] & "#, "
End If

If Not IsNull(Me![tbWarranteeUntil]) Then
SQLFields = SQLFields & "WarranteeUntil, "
SQLValues = SQLValues & "#" & Me![tbWarranteeUntil] & "#, "
End If

If Not IsNull(Me![tbTechFile]) Then
SQLFields = SQLFields & "TechnicalFile, "
SQLValues = SQLValues & "'" & Me![tbTechFile] & "', "
End If

If Not IsNull(Me![tbMFR]) Then
SQLFields = SQLFields & "Manufacturer, "
SQLValues = SQLValues & "'" & Me![tbMFR] & "', "
End If

'this field you hard coded to = 1
'If Not IsNull(Me![tblOutOfService]) Then
SQLFields = SQLFields & "OutOfService, "
SQLValues = SQLValues & 1 & ", "
'SQLValues = SQLValues & Me![tblOutOfService] & ", "
'End If

If Not IsNull(Me![tbOutUntil]) Then
SQLFields = SQLFields & "OutUntil, "
SQLValues = SQLValues & "#" & Me![tbOutUntil] & "#, "
End If

If Not IsNull(Me![tbTakenOut]) Then
SQLFields = SQLFields & "TakenOut, "
SQLValues = SQLValues & "#" & Me![tbTakenOut] & "#, "
End If

If Not IsNull(Me![tbLastReading]) Then
SQLFields = SQLFields & "LastReadingDate, "
SQLValues = SQLValues & "#" & Me![tbLastReading] & "#, "
End If

If Not IsNull(Me![tbCurrentHours]) Then
SQLFields = SQLFields & "CurrentHours, "
SQLValues = SQLValues & Me![tbCurrentHours] & ", "
End If

If Not IsNull(Me![tbCurrentMiles]) Then
SQLFields = SQLFields & "CurrentMiles, "
SQLValues = SQLValues & Me![tbCurrentMiles] & ", "
End If

If Not IsNull(Me![tbCurrentKM]) Then
SQLFields = SQLFields & "CurrentKM, "
SQLValues = SQLValues & Me![tbCurrentKM] & ", "
End If

SQLFields = Left(SQLFields, Len(SQLFields) - 2) & ")"
SQLValues = Left(SQLValues, Len(SQLValues) - 2) & ");"

MySQL = "INSERT INTO Equipment " & SQLFields & " " & SQLValues
'MsgBox MySQL




Steve
Click to expand...
Click to expand...
Click to expand...
 
D

Dirk Goldgar

Dou said:
The following two SQL's error message is the same.
1.

MySQL = "INSERT INTO [Equipment] VALUES('" & Nz(cbGroupCat) &
"','" & Nz(tbEquipNo) & "',""" & Nz(tbEquipDesc) & """,""" &
Nz(tbAssetNo) & """,""" & Nz(tbPartNo) & """,""" & Nz(tbModelNo) &
""",""" & Nz(tbSerialNo) & """,""" & Nz(tbDetails) & """," &
Nz(tbOriginCost) & ",'" & Nz(tbLocation) & "',#" &
Nz(tbPurchasedDate) & "#,#" & Nz(tbInstalledDate) & "#,#" &
Nz(tbWarranteeUntil) & "#,""" & Nz(tbTechFile) & """,""" & Nz(tbMFR)
& """,1,#" & Nz(tbOutUntil) & "#,#" & Nz(tbTakenOut) & "#,#" &
Nz(tbLastReading) & "#," & Nz(tbCurrentHours) & "," &
Nz(tbCurrentMiles) & "," & Nz(tbCurrentKM) & ");"
Click to expand...

As far as this one is concerned, I specifically stated that you should
use the second operand of the Nz() function to supply the string "Null"
to the SQL string. My example was

... & Nz(tbOriginCost, "Null") & ...

You've left out that operand.

It would be a very good idea to insert the statement

Debug.Print MySQL

before attempting to execute the statement, and look at the actual
statement you've built in the Immediate Window to see what's wrong with
it. If you can't tell, copy and paste it into a reply message.
 
D

Dou

The immediate window display the message:

INSERT INTO [Equipment]
VALUES('','123456',"123456","","","","","",,'',##,##,##,"","",1,##,##,##,,,)
;

What's wrong with this statement?


Dirk Goldgar said:
Dou said:
The following two SQL's error message is the same.
1.

MySQL = "INSERT INTO [Equipment] VALUES('" & Nz(cbGroupCat) &
"','" & Nz(tbEquipNo) & "',""" & Nz(tbEquipDesc) & """,""" &
Nz(tbAssetNo) & """,""" & Nz(tbPartNo) & """,""" & Nz(tbModelNo) &
""",""" & Nz(tbSerialNo) & """,""" & Nz(tbDetails) & """," &
Nz(tbOriginCost) & ",'" & Nz(tbLocation) & "',#" &
Nz(tbPurchasedDate) & "#,#" & Nz(tbInstalledDate) & "#,#" &
Nz(tbWarranteeUntil) & "#,""" & Nz(tbTechFile) & """,""" & Nz(tbMFR)
& """,1,#" & Nz(tbOutUntil) & "#,#" & Nz(tbTakenOut) & "#,#" &
Nz(tbLastReading) & "#," & Nz(tbCurrentHours) & "," &
Nz(tbCurrentMiles) & "," & Nz(tbCurrentKM) & ");"
Click to expand...

As far as this one is concerned, I specifically stated that you should
use the second operand of the Nz() function to supply the string "Null"
to the SQL string. My example was

... & Nz(tbOriginCost, "Null") & ...

You've left out that operand.

It would be a very good idea to insert the statement

Debug.Print MySQL

before attempting to execute the statement, and look at the actual
statement you've built in the Immediate Window to see what's wrong with
it. If you can't tell, copy and paste it into a reply message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dou

If I don't left the numeric and date empty, it can insert a record.
Like the following statement it can work.

INSERT INTO [Equipment]
VALUES('', '5765476', '54765476', '', '', '', '', '', 56476, '',
#2004-1-20#, #2004-1-13#, #2004-1-21#, '', '', 1, #2004-1-29#, #2004-1-29#,
#2004-1-21#, 546, 456, 46);

the following statement can't work:

INSERT INTO [Equipment]
VALUES('', '5765476', '54765476', '', '', '', '', '', , '', ##, ##, ##, '',
'', 1, ##, ##, ##, , ,);

Why?
 
J

John Vinson

The immediate window display the message:

INSERT INTO [Equipment]
VALUES('','123456',"123456","","","","","",,'',##,##,##,"","",1,##,##,##,,,)
;

What's wrong with this statement?
Click to expand...

The commas with nothing between them (which should have the word NULL
instead), the commas at the end, the ## with no date value in between,
and the lack of fieldnames to match with the values; if the table
[Equipment] has more or fewer than 21 fields, that'll be a problem
too.
 
D

Dirk Goldgar

Dou said:
The immediate window display the message:

INSERT INTO [Equipment]
VALUES('','123456',"123456","","","","","",,'',##,##,##,"","",1,##,##,##
,,,)
;

What's wrong with this statement?
Click to expand...

All those empty values -- ## and ,, -- that's what. It's exactly as I
said: if you want to insert Null values into the table, you must
specify Null for those values in the SQL statement's VALUES clause, or
else provide a specific field list to match only the non-Null values, as
SteveS suggested. Here, this may be a simpler way to approach it:

First paste the following function in a standard module:

----- start of function code -----
Public Function fncSQLLiteral( _
ArgValue As Variant, ValType As Integer) _
As String

Select Case ValType

Case dbDate
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = Format(ArgValue, "\#mm/dd/yyyy\#")
End If

Case dbText, dbMemo
If IsNull(ArgValue) Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = _
Chr(34) & _
Replace(ArgValue, """", """""", , , 0) & _
Chr(34)
End If

Case Else
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = ArgValue
End If

End Select

End Function
----- end of function code -----

Then modify your assignment to MySQL as follows:

MySQL = _
"INSERT INTO [Equipment] VALUES(" & _
fncSQLLiteral(Me![cbGroupCat], dbText) & _
"," & fncSQLLiteral(Me![tbEquipNo], dbText) & _
"," & fncSQLLiteral(Me![tbEquipDesc], dbText) & _
"," & fncSQLLiteral(Me![tbAssetNo], dbText) & _
"," & fncSQLLiteral(Me![tbPartNo], dbText) & _
"," & fncSQLLiteral(Me![tbModelNo], dbText) & _
"," & fncSQLLiteral(Me![tbSerialNo], dbText)

MySQL = MySQL & _
"," & fncSQLLiteral(Me![tbDetails], dbText) & _
"," & fncSQLLiteral(Me![tbOriginCost], dbCurrency) & _
"," & fncSQLLiteral(Me![tbLocation], dbText) & _
"," & fncSQLLiteral(Me![tbPurchasedDate], dbDate) & _
"," & fncSQLLiteral(Me![tbInstalledDate], dbDate) & _
"," & fncSQLLiteral(Me![tbPurchasedDate], dbDate) & _
"," & fncSQLLiteral(Me![tbWarranteeUntil], dbDate) & _
"," & fncSQLLiteral(Me![tbTechFile], dbText)

MySQL = MySQL & _
"," & fncSQLLiteral(Me![tbMFR], dbText) & _
",1" & _
"," & fncSQLLiteral(Me![tbOutUntil], dbDate) & _
"," & fncSQLLiteral(Me![tbTakenOut], dbDate) & _
"," & fncSQLLiteral(Me![tbLastReading], dbDate) & _
"," & fncSQLLiteral(Me![tbCurrentHours], dbLong) & _
"," & fncSQLLiteral(Me![tbCurrentMiles], dbLong) & _
"," & fncSQLLiteral(Me![tbCurrentKM], dbLong) & _
");"

(I had to break up the assignment into three statements due to the limit
on the number of line continuations.)

I may have overlooked or misread something, but that should get you
pretty close.
 
D

Dou

Thank you very much, I have solved my problem.



Dirk Goldgar said:
Dou said:
The immediate window display the message:

INSERT INTO [Equipment]
VALUES('','123456',"123456","","","","","",,'',##,##,##,"","",1,##,##,##
,,,)
;

What's wrong with this statement?
Click to expand...

All those empty values -- ## and ,, -- that's what. It's exactly as I
said: if you want to insert Null values into the table, you must
specify Null for those values in the SQL statement's VALUES clause, or
else provide a specific field list to match only the non-Null values, as
SteveS suggested. Here, this may be a simpler way to approach it:

First paste the following function in a standard module:

----- start of function code -----
Public Function fncSQLLiteral( _
ArgValue As Variant, ValType As Integer) _
As String

Select Case ValType

Case dbDate
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = Format(ArgValue, "\#mm/dd/yyyy\#")
End If

Case dbText, dbMemo
If IsNull(ArgValue) Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = _
Chr(34) & _
Replace(ArgValue, """", """""", , , 0) & _
Chr(34)
End If

Case Else
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = ArgValue
End If

End Select

End Function
----- end of function code -----

Then modify your assignment to MySQL as follows:

MySQL = _
"INSERT INTO [Equipment] VALUES(" & _
fncSQLLiteral(Me![cbGroupCat], dbText) & _
"," & fncSQLLiteral(Me![tbEquipNo], dbText) & _
"," & fncSQLLiteral(Me![tbEquipDesc], dbText) & _
"," & fncSQLLiteral(Me![tbAssetNo], dbText) & _
"," & fncSQLLiteral(Me![tbPartNo], dbText) & _
"," & fncSQLLiteral(Me![tbModelNo], dbText) & _
"," & fncSQLLiteral(Me![tbSerialNo], dbText)

MySQL = MySQL & _
"," & fncSQLLiteral(Me![tbDetails], dbText) & _
"," & fncSQLLiteral(Me![tbOriginCost], dbCurrency) & _
"," & fncSQLLiteral(Me![tbLocation], dbText) & _
"," & fncSQLLiteral(Me![tbPurchasedDate], dbDate) & _
"," & fncSQLLiteral(Me![tbInstalledDate], dbDate) & _
"," & fncSQLLiteral(Me![tbPurchasedDate], dbDate) & _
"," & fncSQLLiteral(Me![tbWarranteeUntil], dbDate) & _
"," & fncSQLLiteral(Me![tbTechFile], dbText)

MySQL = MySQL & _
"," & fncSQLLiteral(Me![tbMFR], dbText) & _
",1" & _
"," & fncSQLLiteral(Me![tbOutUntil], dbDate) & _
"," & fncSQLLiteral(Me![tbTakenOut], dbDate) & _
"," & fncSQLLiteral(Me![tbLastReading], dbDate) & _
"," & fncSQLLiteral(Me![tbCurrentHours], dbLong) & _
"," & fncSQLLiteral(Me![tbCurrentMiles], dbLong) & _
"," & fncSQLLiteral(Me![tbCurrentKM], dbLong) & _
");"

(I had to break up the assignment into three statements due to the limit
on the number of line continuations.)

I may have overlooked or misread something, but that should get you
pretty close.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
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

Can't insert record into a table 6
Newbie table Layout (Posted as suggested by Tom Lake for feedback) 2
Can't insert records into the table 2
Need Feedback on table setup 1
Cannot synch info or enter data into form 1
can't insert a record into the table 3
table columns don't show when table opened or in query output disp 1
Insert into (dates format) VBA 6

Top