Form help

[LeeSmith]

Hi

Apologies if this has been answered before, but I cant find it anywhere.

I am currently trying to open a form and show records on a specific date. I have created a form which isn't bound to a table and has one field for the date which is being searched for and a button to open the form that has been entered.

Im using the code below, but when I enter a date and click on the button, the form that it opens appears blank. I have checked multiple times and there are definitely records for the date I am searching for. Could anyone help shed any light on this for me or suggest a better way that I could do this?

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRecordsByDate"

stLinkCriteria = "[Date]=" & "#" & Me![datSearch] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub

 

[DCrake]

Simple Software Solutions

Hi Lee

One thing Don't Use the word Date as name for a field. See reserved words in Access. This may be the root of your problem. Also try passing the date to the underlying recordset as opposed to the filter.

CodeMaster:

 

[LeeSmith]

I've tried it and it's still coming up blank :S

 

[missinglinq]

These things can be real picky when stringing criteria together. Instead of

"[YourDate]="

try

"[YourDate]= "

with that space between the equal sign and your quote.

Also the advice about using Date as an object name is dead on! Date is the most dangerous reserved word out there! If you have a field named Date and you do something like this

Date = #1/1/2009#

to assign New Year's Day to the field, what you're actually doing is resetting the system date on your PC!

 

[boblarson]

Also, make sure your date/time field in the table is set to SHORT DATE format or else you will need to change the way you are entering the criteria. If it is storing time too (General Date) then you will need to include time as well as the date.

 

[LeeSmith]

As a follow up to this, I have now discovered the root cause of the problem. When executing the VB code it searches for the date in mm/dd/yyyy format, where as I need it to search in dd/mm/yyyy format.

My code currently looks like this and gives an overflow error on execution.

Private Sub cmdDat_Click()
On Error GoTo Err_cmdDat_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRecordsByDate"

stLinkCriteria = "[EventDate]=" & "#" & Format(Me![datSearch] & "#", dd / mm / yyyy)


DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdDat_Click:
Exit Sub

Err_cmdDat_Click:
MsgBox Err.Description
Resume Exit_cmdDat_Click

End Sub

 

[gemma-the-husky]

do this - first put extra spaces before and after the = next to event date - it can cause problems otherwise

you may have the hashes in the wrong place

i also dont think you want the bang sign before the [datsearch]

try this

stLinkCriteria = "[EventDate] = " & "#" & Format([datSearch],"long date") & "#"

useing format long date changes your date to eg 12th march 2008, and i think it avoids any ambiguity with local date settings - its also easier to remember

 

[missinglinq]

tLinkCriteria = "[EventDate]=" & "#" & Format(Me![datSearch] & "#", dd / mm / yyyy)

You still haven't placed a space after the equal sign as you've been told to do three times by the responders here! Should be "[EventDate]= ".

You don't have quotation marks around your format string and you have extra spaces; should be "dd/mm/yyyy".

If you plan to work in VBA (or any language, for that matter) you simply have to pay attention to detail.There may be other things, but these need to be addressed first.

 

[LeeSmith]

I put a space next to = and long date in the formatting, it comes up blank still as opposed to the overflow error

 

[DCrake]

Simple Software Solutions

To ensure that both formats are the same change it to

Format([EventDate],"Long Date") = #" & Format([datSearch],"Long Date") & "#"