Date format in my form

[Guest]

In my form i have txtNumber and txtDate. If i typed for example to 'txtDate'
field = "02/26/2006" and my txtNumber ="11" then i need to update my table.
For example my table named 'tblDates' with filds name Number and date, i have
3 records where Number="11" in my table. I will need to update to this;
ID Number date
1 11 02/26/2006
2 11 03/26/2006
3 11 04/26/2006

What format should i use for date?
Thank you.

 

[Rick Brandt]

In my form i have txtNumber and txtDate. If i typed for example to
'txtDate' field = "02/26/2006" and my txtNumber ="11" then i need to
update my table. For example my table named 'tblDates' with filds
name Number and date, i have 3 records where Number="11" in my table.
I will need to update to this;
ID Number date
1 11 02/26/2006
2 11 03/26/2006
3 11 04/26/2006

What format should i use for date?
Thank you.

Date constants used in a query need to be either US format (numeric
Month-Day-Year), or in ISO format (numeric 4DigitYear-Month-Day), or in a format
that uses alpha characters for the month (like mmm-dd-yy or mmmm dd, yyyy).

You could enter any recognizable date format and use CDate() wrapped around the
value in your query but that would convert the value to a date using the
regional settings on the computer and thus could potentially be inconsistent
from one PC to another.

 

[Guest]

Some thing wrong with this code, it does insert records base on what number i
will type for field txtRepeatNum, but it's not working for my start date. For
example if i add for txtRepeatNum= 3, txtNumber=10 and txtStartDt=02/26/2006
then it will insert three records to my table, it should look like this
(every next record should give date for next motn);
ID Number date
1 11 02/26/2006
2 11 03/26/2006
3 11 04/26/2006


==========
Private Sub cmdCreate_Click()

Dim strNum As String
Dim strStartDt As Integer
Dim intCtr As Integer
Dim strSQL As String

strNum = Me.txtNumber
strStartDt = Me.txtStartDt

For intCtr = 1 To Me.txtRepeatNum
strStartDt = DateAdd("mm/dd/yyyy", 1, strStartDt) ''I think i am using
wrong format.

strSQL = "INSERT INTO tblRepeatDates (Number, StartDt) SELECT '" & strNum &
"' AS Expr1, '" & CDbl(strStartDt) & "' AS Expr3 "
DoCmd.RunSQL strSQL

CurrentDb.Execute strSQL, dbFailOnError
Next intCtr

End Sub
========================

 

[Guest]

I've made small changes to my code but still i am trying to find out how can
i populate correctly the start date.

==========
Private Sub cmdCreate_Click()

Dim strNum As String
Dim strStartDt As Date
Dim intCtr As Integer
Dim strSQL As String

strNum = Me.txtNumber
strStartDt = Me.txtStartDt

For intCtr = 1 To Me.txtRepeatNum
strStartDt = DateAdd("mm/dd/yyyy", 1, strStartDt) ''I think i am using
wrong format.

strSQL = "INSERT INTO tblRepeatDates (Number, StartDt) SELECT '" & strNum &
"' AS Expr1, '" & CDate(strStartDt) & "' AS Expr3 "
DoCmd.RunSQL strSQL

CurrentDb.Execute strSQL, dbFailOnError
Next intCtr

End Sub
========================

 

[Douglas J. Steele]

Your DateAdd function is wrong.

To add 1 day, use:

strStartDt = DateAdd("d", 1, strStartDt)

You cannot use SELECT in conjunction with INSERT INTO unless you're
selecting from a table. Try

strSQL = "INSERT INTO tblRepeatDates (Number, StartDt) " & _
"VALUES (" & strNum & ", " & CDbl(strStartDt) & ")"

If that doesn't work, try:

strSQL = "INSERT INTO tblRepeatDates (Number, StartDt) " & _
"VALUES (" & strNum & ", " & Format(strStartDt, "\#mm\/dd\/yyyy\#") & ")"



This assumes that Number is a numeric field. If it isn't, change the second
line to

"VALUES ('" & strNum & "', " & CDbl(strStartDt) & ")"

You should rename your field, though: Number is a reserved word, so
shouldn't be used for your own purposes.

 

[Guest]

Doug

Thank you, so much. It works perfectly.

Your DateAdd function is wrong.

To add 1 day, use:

strStartDt = DateAdd("d", 1, strStartDt)

You cannot use SELECT in conjunction with INSERT INTO unless you're
selecting from a table. Try

strSQL = "INSERT INTO tblRepeatDates (Number, StartDt) " & _
"VALUES (" & strNum & ", " & CDbl(strStartDt) & ")"

If that doesn't work, try:

strSQL = "INSERT INTO tblRepeatDates (Number, StartDt) " & _
"VALUES (" & strNum & ", " & Format(strStartDt, "\#mm\/dd\/yyyy\#") & ")"



This assumes that Number is a numeric field. If it isn't, change the second
line to

"VALUES ('" & strNum & "', " & CDbl(strStartDt) & ")"

You should rename your field, though: Number is a reserved word, so
shouldn't be used for your own purposes.