Date Data Type Behavior

[StlMacMan]

I received some help about how to populate form fields from an Access table and then save data to another table. Thanks. Using those tips I have been able to include several form fields in my "save" process, but have come to a "date" field which will not behave the same way as the other text fields. I have Dimmed the Date field and the Access field is a date field as well, so I'm not sure why it alone is stopping the program. I can't find any documentation that suggests there is something particularly different about date data type, but can't get past this error. Any ideas? --Ed

 

[kav123]

Can you tell what is the exact error you are getting, maybe you need to format the date in the form similar to the one you have created in database table(whether it is a short date, long date etc)

 

[StlMacMan]

Kav-- Here's what I get:
Compile Error: Syntax Error

Here's the code that generates the error. I have been adding fields and the two dates don't work--all the others did until I put the dates in. What do you think?

Private Sub Command1_Click()

Dim TxtCtrtStartDate As Date
Dim TxtCtrtEndDate As Date

Set Con = New ADODB.Connection
Set rs = New ADODB.Recordset
Con.Open strCon

Con.Execute "INSERT INTO Contract (Corps_Institution, Program, Funder, Contract_Amount, Contract_Number, _
Start_Date, End_Date) _
VALUES ('" & CboUnit.Text & "','" & CboProgram.Text & "','" & _
CboFunder.Text & "','" & Text4.Text & "','" & TxtCtrtNo(0).Text & "','" & _
TxtCtrtStartDate.Text & "','" & TxtCtrtEndDate.Text & "')"

Con.Close
Set Con = Nothing
End Sub

 

[Spellman]

hey StlMacMan ,

couple of problems with this. First off you declare both your TxtCtrtStartDate and TxtCtrtEndDate as date types. I assume on the form you created you have text boxes of the same name. This in itself will throw up an error. Remove the Dim of these two variables at the start of the commnad1_click process and see how that works. If you don't have text boxes of that name then these are just variables and have not been assigned any value and also variables you declare do not have properties i.e .Text.

Mark

The key to immortality is to make a big impression in this life!!

 

[RoyVidar]

Working with Access, I think the dates will need # as delimiter, and if your regional date settings differ from US, perhaps also an unambiguous date format

[tt] ...,#" format(TxtCtrtStartDate.Text,"yyyy-mm-dd") & _
"#,#" & format(TxtCtrtEndDate.Text,"yyyy-mm-dd") & "#)"[/tt]

Roy-Vidar

 

[bjd4jc]

Just to follow up on RoyVidar, it is not necessary to format the date before you put it between the pound (#) signs. You will want to validate any dates the user enters before putting it between the pound signs.

 

[bjd4jc]

Oh, and the reason for this is because of how the date is stored. It is not stored as text at all so the separator characters are not stored in the database. That is all based on your regional settings. Dates are actually stored as numbers.

 

[StlMacMan]

O.K. Guys--

Yes, Spellman they are text boxes for date entries. I deleted the Dim statements. Thank you.

I deleted all the working elements in the earlier code and have only the one date as follows to simplify testing. Still a no-go. I believe I followed Roy V's syntax though w/o the format since I checked my regional settings and they match the db short-date format. Thank you Roy and BJD.


Private Sub Command1_Click()


Set Con = New ADODB.Connection
Set rs = New ADODB.Recordset
Con.Open strCon

Con.Execute "INSERT INTO Contract (Start_Date) _
VALUES (# " & TxtCtrtStartDate.Text & " #)"

Con.Close
Set Con = Nothing
End Sub

 

[bjd4jc]

what error are you getting?

 

[StlMacMan]

The error message is:

Compile Error: Syntax Error

Clicking OK gets me this entire statement highlighted.

Con.Execute "INSERT INTO Contract (Start_Date) _
VALUES (# " & TxtCtrtStartDate.Text & " #)"

Any help appreciated.--Ed

 

[RoyVidar]

Text concatenation & and _ :

[tt]Con.Execute "INSERT INTO Contract (Start_Date) " & _
"VALUES (#" & TxtCtrtStartDate.Text & "#)"[/tt]

Roy-Vidar

 

[StlMacMan]

Thank you all for the help. Finally got it. Now I just need to include the other 10 dates in the tracking section as part of the save and it should be ready to run. Again. Thank you all very much. --Ed