error 3075...making me nuts

[June7]

Can't save that value into date type field. The Format function results in a string, not a date. Date is actually stored in a date type field as a number. I don't even see why you are using Format function.

CurrentDb.Execute "INSERT INTO companytable (ticker, company, sector, subindustry, lastupdate, flag) " & _


" VALUES('" & Me.cboTicker & "', '" & Me.txtCompany & "', '" & Me.txtSector & "', '" & Me.txtSubIndustry & "', #" & _
Me.dtLastupdate & "#, " & me.cbxFlag & ")"

[pbaldy]

I'll get out of the way, but the Format() function is very commonly used to ensure a valid date is passed:

http://allenbrowne.com/ser-36.html#Format

http://access.mvps.org/access/datetime/date0005.htm

[JoeM]

Just a little hint when using VBA code to create SQL code to run.

The method that I often use is the following:
1. Create a query using Query Builder or SQL that is an example of what I want to do. Get it working exactly right (without errors).
2. Once it is working correctly, switch to SQL view and copy and paste the SQL code out to a text file or Word Document.
3. Write your VBA code to create the SQL code you want. Before executing it, write it out to a Message Box.
4. Compare the SQL code in your Message Box to the SQL code in your text file/Word Document and look for differences and make the necessary adjustments.
5. Once you get them to match, it should work!

[graccess]

Just a little hint when using VBA code to create SQL code to run.

The method that I often use is the following:
1. Create a query using Query Builder or SQL that is an example of what I want to do. Get it working exactly right (without errors).
2. Once it is working correctly, switch to SQL view and copy and paste the SQL code out to a text file or Word Document.
3. Write your VBA code to create the SQL code you want. Before executing it, write it out to a Message Box.
4. Compare the SQL code in your Message Box to the SQL code in your text file/Word Document and look for differences and make the necessary adjustments.
5. Once you get them to match, it should work!

Thanks Joe. Good advice. I've tried adding code to produce the exception caught in a message box but I'm apparently not inputting that correctly. How would you go about doing this?
thx

[graccess]

I'll get out of the way, but the Format() function is very commonly used to ensure a valid date is passed:

http://allenbrowne.com/ser-36.html#Format

http://access.mvps.org/access/datetime/date0005.htm

Thanks again. I think I'm running into trouble where I'm already assigning the field name dtLastupdate with a date (the show date picker in the format tab properties is set to "For Dates".) Not sure if I'm creating more problems by also typing in the code for this when trying to insert these values?

[JoeM]

Thanks Joe. Good advice. I've tried adding code to produce the exception caught in a message box but I'm apparently not inputting that correctly. How would you go about doing this?

Instead of doing it like this:

CurrentDb.Execute "INSERT INTO companytable (ticker, company, sector, subindustry, lastupdate, flag) " & _
" VALUES('" & Me.cboTicker & "', '" & Me.txtCompany & "', '" & Me.txtSector & "', '" & Me.txtSubIndustry & "', #" & _
Me.dtLastupdate & "#, " & me.cbxFlag & ")"

try it like this:

Dim mySQL as String

' Build SQL Code to run and store as string
mySQL = "INSERT INTO companytable (ticker, company, sector, subindustry, lastupdate, flag) " & _
" VALUES('" & Me.cboTicker & "', '" & Me.txtCompany & "', '" & Me.txtSector & "', '" & Me.txtSubIndustry & "', #" & _
Me.dtLastupdate & "#, " & me.cbxFlag & ")"

' View SQL code before running to make sure it matches your sample SQL code gained from running query manually
MsgBox mySQL

' Execute SQL code (only after sure everything is correct)
CurrentDb.Execute mySQL

I sometimes use:

DoCmd.RunSQL mySQL

instead of:

CurrentDb.Execute mySQL

[June7]

Agree Paul, but I think the structure must be MM/DD/YYYY and delimited in the query with #. That's what Allen's function code does. The backslash forces the following character to be accepted as the literal character so it will pass within the variable and not have to be separately included in the query statement concatenation.

However, this code won't help if the users are inputting into textbox in a structure the system is not defaulting to. As a US user, if I enter 7/4/2014 intending 7 Apr 2014, Access actually sees Jul 4 2014 and records that date. Now my data is bad. If I input 31/12/2014, Access will do me a 'favor' and flip it to 12/31/2014.

[pbaldy]

I think the structure must be MM/DD/YYYY and delimited in the query with #.

The ISO format in the OP is perfectly valid (just tested), and preferred by some developers as being unambiguous. The # were included as required in the OP, the problem was with the additional text delimiters around the value, as evidenced in the original error. As yet we haven't seen the finished SQL.

[graccess]

Instead of doing it like this:

CurrentDb.Execute "INSERT INTO companytable (ticker, company, sector, subindustry, lastupdate, flag) " & _
" VALUES('" & Me.cboTicker & "', '" & Me.txtCompany & "', '" & Me.txtSector & "', '" & Me.txtSubIndustry & "', #" & _
Me.dtLastupdate & "#, " & me.cbxFlag & ")"

try it like this:

Dim mySQL as String

' Build SQL Code to run and store as string
mySQL = "INSERT INTO companytable (ticker, company, sector, subindustry, lastupdate, flag) " & _
" VALUES('" & Me.cboTicker & "', '" & Me.txtCompany & "', '" & Me.txtSector & "', '" & Me.txtSubIndustry & "', #" & _
Me.dtLastupdate & "#, " & me.cbxFlag & ")"

' View SQL code before running to make sure it matches your sample SQL code gained from running query manually
MsgBox mySQL

' Execute SQL code (only after sure everything is correct)
CurrentDb.Execute mySQL

I sometimes use:

DoCmd.RunSQL mySQL

instead of:

CurrentDb.Execute mySQL

Joe...thank you so much. Not only for the msgbox code/tip, but for correctly displaying my code, I was indeed missing my apostrophe mistakes in addition to the date syntax. Thanks again.

[JoeM]

I was indeed missing my apostrophe mistakes in addition to the date syntax. Thanks again.

Your welcome. Glad you got it all sorted out!