Append a Date From a Form?

[Hakala]

Hi, everyone!

I'm appending data from one table into another using an interactive form. The user enters a date, and I want the appended data to include that date. However, my append query is giving me a type mismatch error. The data imports fine without the date included.

The table receiving is set to Short Date on that date field and the data type for the user's data entry is also set to Short Date. The original data table includes no dates at all.

In the append query, I've got the field set to: Effective: [Forms]![frmSRENExcelImport]![txtEffective] and it is supposed to save to the field EffectiveDate in the receiving table. If I change the append query to a select query, I can see the date in the data, but it won't save it to the receiving table.

Does anyone have any idea what I might be doing wrong? (Let me know if I need to provide more information, or if I should be posting this in a different forum.)

Thank you!

 

[randy700]

In your query, make sure the date is surrounded by # symbols.
# & [Forms]![frmSRENExcelImport]![txtEffective] & #


Randy

 

[Hakala]

Thanks, Randy!

However, I can't find anywhere to do that... If I add it in the append query on the Field line, it tells me the expression I entered has an invalid date value.

If I add it in the SQL of the query, I get run-time error 3075 (Syntax error).

Urk.

 

[randy700]

Can you post the SQL you have?


Randy

 

[Hakala]

This is the original:

INSERT INTO tblDataImport ( GroupNumber, CustomerName, TOW, CustomerNumber, EffectiveDate )
SELECT DISTINCT DHMO.[Group Id] AS GroupNumber, DHMO.[Group Name] AS CustomerName, "DHMO" AS TOW, DHMO.RTMS, [Forms]![frmSRENExcelImport]![txtEffective] AS Effective
FROM DHMO
WHERE (((DHMO.[Group Id]) Is Not Null));

This one gives me a type conversion error and puts no dates in the table (although all the other data comes through fine)
and this is the new way with the # added:

INSERT INTO tblDataImport ( GroupNumber, CustomerName, TOW, CustomerNumber, EffectiveDate )
SELECT DISTINCT DHMO.[Group Id] AS GroupNumber, DHMO.[Group Name] AS CustomerName, "DHMO" AS TOW, DHMO.RTMS, # & [Forms]![frmSRENExcelImport]![txtEffective] & # AS Effective
FROM DHMO
WHERE (((DHMO.[Group Id]) Is Not Null));

and this way gives me the run-time error.

I'm sure this is something simple; it's usually those things that take me all day!

 

[Hakala]

I used the code from this post: thread705-1246792 to set my Date as SQLDate and it's working.

I'm using MajP's function from the other post:

 Function SQLDate(varDate As Variant) As String
   If IsDate(varDate) Then
      SQLDate = "#" & Format$(varDate, "yyyy-mm-dd") & "#"
   End If
 End Function

and changed my query to:

 INSERT INTO tblDataImport ( GroupNumber, CustomerName, TOW, CustomerNumber, EffectiveDate )
SELECT DISTINCT DHMO.[Group Id] AS GroupNumber, DHMO.[Group Name] AS CustomerName, "DHMO" AS TOW, DHMO.RTMS, SQLDate([Forms]![frmSRENExcelImport]![txtEffective]) AS Effective
FROM DHMO
WHERE (((DHMO.[Group Id]) Is Not Null));

Are there any dangers to doing it this way?