Append Query help

[cableguy47905 via AccessMonster.com]

I am trying to do an append query on the fly. I have several different types
of textboxes, check boxes, and combo boxes as input. The ones that I seem to
have trouble with are the memo fields or combo boxes. Below is the SQL code
that I have printed out to help debug.

INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe, file_format,
fields_ignore, logic, map_status, Business_Need, PPED_map, Comments_map,
transfer_date, test_file, revision_change) VALUES (NewMap,FTP,H244ftp,
delimited,none,none,New Request,Technology Issue,-1,none,,none,none);

In the immediate window, I hover over the "fields_ignore" and it says null,
so does "test_file". However, you can see that it is pulling in a value.
Why wouldn't it go ahead and insert it?

I get a pop up when I try to run this statement. The error is: Run-time
error 3075:
Syntax error (missing operator) in query expression 'New Request'.

If anyone would be able to help me out with the syntax, I would greatly
appreciate it.

Thanks,
Lee

 

[Ken Snell \(MVP\)]

You must concatenate ' delimiters around all text strings that are being
inserted into the table. The end result of the SQL string should be this:

INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe, file_format,
fields_ignore, logic, map_status, Business_Need, PPED_map, Comments_map,
transfer_date, test_file, revision_change) VALUES ('NewMap','FTP','H244ftp',
'delimited','none','none','New Request','Technology
Issue',-1,'none',,'none','none');

Also you need to supply a value to go into the "transfer_date" field; your
SQL statement has no value for it.

 

[Jana]

I am trying to do an append query on the fly. I have several different types
of textboxes, check boxes, and combo boxes as input. The ones that I seem to
have trouble with are the memo fields or combo boxes. Below is the SQL code
that I have printed out to help debug.

INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe, file_format,
fields_ignore, logic, map_status, Business_Need, PPED_map, Comments_map,
transfer_date, test_file, revision_change) VALUES (NewMap,FTP,H244ftp,
delimited,none,none,New Request,Technology Issue,-1,none,,none,none);

In the immediate window, I hover over the "fields_ignore" and it says null,
so does "test_file". However, you can see that it is pulling in a value.
Why wouldn't it go ahead and insert it?

I get a pop up when I try to run this statement. The error is: Run-time
error 3075:
Syntax error (missing operator) in query expression 'New Request'.

If anyone would be able to help me out with the syntax, I would greatly
appreciate it.

Thanks,
Lee

Lee:
If you need to insert the text 'New Request' into the map_status field
of the TBL_Map, then you need to wrap it with single quotes. Same
thing goes for Technology Issue.

So, you should change it as follows:
INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe,
file_format,
fields_ignore, logic, map_status, Business_Need, PPED_map,
Comments_map,
transfer_date, test_file, revision_change) VALUES (NewMap,FTP,H244ftp,
delimited,none,none,'New Request','Technology
Issue',-1,none,,none,none);

We might be better able to help you if you post the entire code that
is building your SQL string, since it is not clear where you're
getting the values from.

HTH,
Jana

 

[cableguy47905 via AccessMonster.com]

Thank you both very much for your quick replies. I am having trouble adding
the single quotes to the string. Each time I do, it thinks that the rest of
the string is just comments. How do I add the single quotes to the VBA
string?

This is what I currently have that creates the string below:
stMapSQL = "INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe,
file_format, fields_ignore, logic, map_status, Business_Need, PPED_map,
Comments_map, transfer_date, test_file, revision_change) VALUES (" & Me!
[txtMapID] & "," & Me![CboSubmittype] & "," & Me![txtPushToMF] & "," & Me!
[txtFileFormat] & "," & Me![txtfields_ignore] & "," & Me![TxtLogic] & "," &
Me![CboMapStatus] & "," & Me![CboBusinessNeed] & "," & Me![chkPPED] & "," &
Me![TxtComments] & "," & Me![txtTransDate] & "," & Me![TxtTestFile] & "," &
Me![txtRevision] & ");"


Thanks again,
Lee

I am trying to do an append query on the fly. I have several different types
of textboxes, check boxes, and combo boxes as input. The ones that I seem to

[quoted text clipped - 22 lines]
Lee:
If you need to insert the text 'New Request' into the map_status field
of the TBL_Map, then you need to wrap it with single quotes. Same
thing goes for Technology Issue.

So, you should change it as follows:
INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe,
file_format,
fields_ignore, logic, map_status, Business_Need, PPED_map,
Comments_map,
transfer_date, test_file, revision_change) VALUES (NewMap,FTP,H244ftp,
delimited,none,none,'New Request','Technology
Issue',-1,none,,none,none);

We might be better able to help you if you post the entire code that
is building your SQL string, since it is not clear where you're
getting the values from.

HTH,
Jana

 

[Douglas J. Steele]

Assuming that, say, submit_type is a text field, rather than

"," & Me![CboSubmittype] & ","

you'd use

",'" & Me![CboSubmittype] & "',"

Exagerated for clarity, that's

", ' " & Me![CboSubmittype] & " ' ,"

That'll work unless the value to be stored contains an apostrophe. In that
case, you need either

",""" & Me![CboSubmittype] & ""","

or

",'" & Replace(Me![CboSubmittype], "'", "''") & "',"

(Again, for clarity, that Replace function call is
Replace(Me![CboSubmittype], " ' ", " ' ' "))

Note that transfer_date is a date field, you need to delimit the value with
#, and you need to ensure that it's in a format that Access will correctly
interpret. Since you have no control over what date format your users may
have chosen in their Regional Settings, and since Access doesn't respect
many of the Regional Settings choices, you're best off using

"," & Format(Me![txtTransDate], \#yyyy\-mm\-dd\#") & ","

if txtTransDate contains a date only, or

"," & Format(Me![txtTransDate], \#yyyy\-mm\-dd hh\:nn\:ss\#") & ","

if it contains both a date and time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you both very much for your quick replies. I am having trouble
adding
the single quotes to the string. Each time I do, it thinks that the rest
of
the string is just comments. How do I add the single quotes to the VBA
string?

This is what I currently have that creates the string below:
stMapSQL = "INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe,
file_format, fields_ignore, logic, map_status, Business_Need, PPED_map,
Comments_map, transfer_date, test_file, revision_change) VALUES (" & Me!
[txtMapID] & "," & Me![CboSubmittype] & "," & Me![txtPushToMF] & "," & Me!
[txtFileFormat] & "," & Me![txtfields_ignore] & "," & Me![TxtLogic] & ","
&
Me![CboMapStatus] & "," & Me![CboBusinessNeed] & "," & Me![chkPPED] & ","
&
Me![TxtComments] & "," & Me![txtTransDate] & "," & Me![TxtTestFile] & ","
&
Me![txtRevision] & ");"


Thanks again,
Lee

I am trying to do an append query on the fly. I have several different
types
of textboxes, check boxes, and combo boxes as input. The ones that I
seem to

[quoted text clipped - 22 lines]
Lee:
If you need to insert the text 'New Request' into the map_status field
of the TBL_Map, then you need to wrap it with single quotes. Same
thing goes for Technology Issue.

So, you should change it as follows:
INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe,
file_format,
fields_ignore, logic, map_status, Business_Need, PPED_map,
Comments_map,
transfer_date, test_file, revision_change) VALUES (NewMap,FTP,H244ftp,
delimited,none,none,'New Request','Technology
Issue',-1,none,,none,none);

We might be better able to help you if you post the entire code that
is building your SQL string, since it is not clear where you're
getting the values from.

HTH,
Jana

 

[cableguy47905 via AccessMonster.com]

Boy, that is a lot of syntax. It will take a little to digest all of that.
I greatly appreciate it, you anticipated my next possible question about the
date fields. That is greatly appreciated as well.

Thanks,
Lee

Assuming that, say, submit_type is a text field, rather than

"," & Me![CboSubmittype] & ","

you'd use

",'" & Me![CboSubmittype] & "',"

Exagerated for clarity, that's

", ' " & Me![CboSubmittype] & " ' ,"

That'll work unless the value to be stored contains an apostrophe. In that
case, you need either

",""" & Me![CboSubmittype] & ""","

or

",'" & Replace(Me![CboSubmittype], "'", "''") & "',"

(Again, for clarity, that Replace function call is
Replace(Me![CboSubmittype], " ' ", " ' ' "))

Note that transfer_date is a date field, you need to delimit the value with
#, and you need to ensure that it's in a format that Access will correctly
interpret. Since you have no control over what date format your users may
have chosen in their Regional Settings, and since Access doesn't respect
many of the Regional Settings choices, you're best off using

"," & Format(Me![txtTransDate], \#yyyy\-mm\-dd\#") & ","

if txtTransDate contains a date only, or

"," & Format(Me![txtTransDate], \#yyyy\-mm\-dd hh\:nn\:ss\#") & ","

if it contains both a date and time.

Thank you both very much for your quick replies. I am having trouble
adding

[quoted text clipped - 48 lines]