By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Error in VBA Code: Date format changing

P: 4
I have a table consisting of a field name "Duty_Date" in a table "DutySchedule"
and a form in which a text box "StartDate" and a command button on the form
Now I have attached following code with the command button

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDateEntry_Click()
  2. Dim i
  3. Dim sql1 As String
  4.  
  5. For i = Me!txtStartDate To DateAdd("d", 7, Me.txtStartDate)
  6. sql1 = "INSERT INTO tblDODutySchedule(duty_date,do_num) VALUES (#" & i & "#,do_num)"
  7. DoCmd.RunSQL sql1
  8. DoCmd.RunMacro "Macro1"
  9. Next i
  10. End Sub
I intend to enter automatically 8 dates in different rows incremented by 1. That is if I enter a value in StartDate textbox in form, say 28/03/2008
I get the table updated by following values
28/03/2008
29/03/2008
30/03/2008
31/03/2008
04/01/2008
04/02/2008
04//03/2008
04/04/2008

you see that the values in last four rows have changed in format dd/mm/yyyy to mm/dd/yyyy. I am unable to figure out reasons of this error.
Can any body provide the right code so that all the values of dates remain in dd/mm/yyyy format. THIS PROBLEM Occurs WHEN MONRHS OVERLAP

Thank You
Jan 11 '08 #1
Share this Question
Share on Google+
2 Replies


Minion
Expert 100+
P: 108
One way you might fix this by adding the date format to the table field directly. I think that would store the date in the desired format regardless of how it was entered. As for the code I think if you format the date before insert it will work, but I may be wrong. Try the following code and see if it works.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDateEntry_Click()
  2. Dim i
  3. Dim sql1 As String
  4.  
  5. For i = Me!txtStartDate To DateAdd("d", 7, Me.txtStartDate)
  6. 'Formating the date here
  7. i = format(i,"dd/mm/yyyy")
  8.  
  9. sql1 = "INSERT INTO tblDODutySchedule(duty_date,do_num) VALUES (#" & i & "#,do_num)"
  10. DoCmd.RunSQL sql1
  11. DoCmd.RunMacro "Macro1"
  12. Next i
  13. End Sub
  14.  
Hope this helps.

- Minion -
Jan 11 '08 #2

P: 4
One way you might fix this by adding the date format to the table field directly. I think that would store the date in the desired format regardless of how it was entered. As for the code I think if you format the date before insert it will work, but I may be wrong. Try the following code and see if it works.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDateEntry_Click()
  2. Dim i
  3. Dim sql1 As String
  4.  
  5. For i = Me!txtStartDate To DateAdd("d", 7, Me.txtStartDate)
  6. 'Formating the date here
  7. i = format(i,"dd/mm/yyyy")
  8.  
  9. sql1 = "INSERT INTO tblDODutySchedule(duty_date,do_num) VALUES (#" & i & "#,do_num)"
  10. DoCmd.RunSQL sql1
  11. DoCmd.RunMacro "Macro1"
  12. Next i
  13. End Sub
  14.  
Hope this helps.

- Minion -
In the mean while I have corrected the code from a clue on "theScript"
the correct code is
Private Sub cmdDateEntry_Click()
Dim i
Dim sql1 As String
For i = Int(CVDate(Me!txtStartDate)) To Int(CVDate(DateAdd("d", 7, Me!txtStartDate)))
sql1 = "INSERT INTO tblDODutySchedule(duty_date,do_num) VALUES ('" + Format(i, "dd/mm/yyyy") + "',do_num)"
DoCmd.RunSQL sql1
DoCmd.RunMacro "Macro1"
Next i
End Sub
And this is working.

Rashid
Jan 11 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.