Format of date in my code


T

Tony Williams

I have a subform with a date called txtmontha with a format mmmm/yyyy There
is also a command button that when clicked should open another subform which
has a date txtmonthlabela, also format as mmm/yyyy. The command button should
open the sub form for all records when the value of txtmontha and
txtmonthlabela are the same. However when I click on the command button
although the form opens I get a prompt box asking me for the value of
txtmonthlabela. If I cancel the prompt box the form opens with a number of
records shown in the record selector box at the foot of the form but no data
is shown in the controls on the form.
Here is my code:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click


Dim strtxtdate As Date
strtxtdate = Me.txtmontha.Value
Forms!frmMain!SubForm1.SourceObject = "frmFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabela] = #" & Format(strtxtdate, "mmmm/yyyy") & "#;"

Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

Can anyone suggest what the problem may be?
Thanks
Tony
 
Ad

Advertisements

M

Marshall Barton

Tony said:
I have a subform with a date called txtmontha with a format mmmm/yyyy There
is also a command button that when clicked should open another subform which
has a date txtmonthlabela, also format as mmm/yyyy. The command button should
open the sub form for all records when the value of txtmontha and
txtmonthlabela are the same. However when I click on the command button
although the form opens I get a prompt box asking me for the value of
txtmonthlabela. If I cancel the prompt box the form opens with a number of
records shown in the record selector box at the foot of the form but no data
is shown in the controls on the form.
Here is my code:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click


Dim strtxtdate As Date
strtxtdate = Me.txtmontha.Value
Forms!frmMain!SubForm1.SourceObject = "frmFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabela] = #" & Format(strtxtdate, "mmmm/yyyy") & "#;"

txtmonthlabela needs to be replaced with the name of the
corresponding field name in table tblmaintabs.
 
D

Douglas J. Steele

mmmm/yyyy isn't a date. Dates must be complete dates: a full day, month and
year.

You cannot use the # delimiter unless you're dealing with dates.
 
Ad

Advertisements

T

Tony Williams

Thanks guys. I had got the incorrect field name in tblmaintabs (dyslectic
fingers!) and once I corrected it the code worked fine.
I know the field is called txtmonthlabel but it is a date field and
formatted as mmmm/yyyy This code now works (I've changed the name of my
destination form to)
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click


Dim strtxtdate As Date
strtxtdate = Me.txtmontha.Value
Forms!frmMain!SubForm1.SourceObject = "subformFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "#;"

Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub
Thanks
Tony
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top