Access VBA DCount Help

dixiedog79

New Member
Joined
Jan 31, 2012
Messages
8
Hi all,

Can anyone help? I have a textbox (formated as medium date) in a form which users will enter a date. I then have a table with a list of dates (again formated as medium date). What I am trying to achieve is for a User to enter a date into the textbox and if it matches a date in the table, then the user can move on with the form. If however they enter a date which isn't in the table, then he says that they need to try again.

The code looks okay, but for some reason it isn't finding any correct data in the table. Can you help... the code is this...

[FONT=&quot]Private Sub DateFrom_AfterUpdate()[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]'If field is populated then move on.[/FONT]
[FONT=&quot] If Not IsNull(Me.DateFrom) Then
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]'If TextBox 'DateFrom' has data which is in table 'tblDatesFiltered' and Row 'strDateCode', then say 'Please Enter a Return Date' - which is the next field. However if there is no match then say '[/FONT][FONT=&quot]Date Not Valid (are you entering a weekend or bank holiday?) - Please Try Again'[/FONT][FONT=&quot].
[/FONT]
[FONT=&quot]If DCount("[strDateCode]", "[tblDatesFiltered]", "[strDateCode]='" & Me.DateFrom) = True Then[/FONT]
[FONT=&quot]MsgBox "Please Enter a Return Date!"[/FONT]
[FONT=&quot]Me.DateTo.SetFocus[/FONT]
[FONT=&quot]Else[/FONT]
[FONT=&quot]MsgBox "Date Not Valid (are you entering a weekend or bank holiday?) - Please Try Again"[/FONT]
[FONT=&quot]Me.DateFrom.SetFocus[/FONT]
[FONT=&quot]End If[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot]End Sub[/FONT]

Hope this makes sence? Any help would be appreciated!!!
Thanks
Paul
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
If DCount("*", "tblDatesFiltered", "[strDateCode]=#" & Me.DateFrom & "#") = 0 Then
 
Last edited:
Upvote 0
Hi Bob... hoping you can help.

I thought it was fine, but I only tested a few dates. The problem I think I have is that the dates on the table are UK format, but the VBA is US. Is there anyway to add UK format to this code? The code I have so far is:

If Not IsNull(Me.DateFrom) Then

If DCount("*", "tblDatesFiltered", "[strDateCode]=#" & Me.DateFrom & "#") = 0 Then
MsgBox "Date Not Valid (are you entering a weekend or bank holiday?) - Please Try Again"
Me.DateFrom.SetFocus
Else
MsgBox "IT's WORKED!"
Me.DateTo.SetFocus
End If
End If

I have tried changing the format of the table to US, but it still doesn't work for me. If I pick 04th February 2012 from the calender, it should tell me that the date isn't valid - however VBA thinks it's 02nd April 2012!

Thanks
Paul
 
Upvote 0
Paul

Do the dates have the ordinals, eg th, nd etc ?
 
Upvote 0
If the answer to Norie's question is "no" then I also need to tell you that you NEED to use U.S. formatted dates in your queries and domain aggregates (thank Microsoft for that one). So you may, depending on how your regional settings are set, need to explicitly use this type of way:


If DCount("*", "tblDatesFiltered", "[strDateCode]=" & Format(Me.DateFrom, "\#mm\/dd\/yyyy\#")) = 0 Then

See here for more about that:
http://allenbrowne.com/ser-36.html
 
Upvote 0
You could try using DateValue.
Code:
"[strDateCode]=#" & DateValue(Me.DateFrom.Value) & "#"
Perhaps even drop the # when you use that.
 
Upvote 0
Thanks all... this seems to have worked well, but need to test it fully tomorrow... but at first site it seems to work.

If DCount("*", "tblDatesFiltered", "[strDateCode]=" & Format(Me.DateFrom, "\#mm\/dd\/yyyy\#")) = 0 Then
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top