Solved

get a date value with dmin in access 2013

Posted on 2013-11-13
10
869 Views
Last Modified: 2013-11-14
hi

i have in microsoft access a vba code that is checking (when i open the form) if there is a date in a date field that the date has  been passed or it's the the  today's date, and is so i show some msgbox. but for some reason i get every time when i open this form a msgbox with this  value 00:00:00 (and there is no date like this in my date field), so why i get this date and how can i Bypass this problem


this is my code
On Error Resume Next
Dim d As Date
d = DMin("ReminderDate", "MecirahIncomeDetailsT", "ReminderDate<=#" & Date & "#")
MsgBox d
If d <= Date Then
    Me.ReminderActiveCmd.Visible = True
    MsgBox d
Else
    Me.ReminderActiveCmd.Visible = False
End If

Open in new window

0
Comment
Question by:bill201
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39644165
Hi,

Are there any records in the table that do not have a value within the [ReminderDate] column?

The returned value of 00:00:00 seems to indicate that you do have at least one value that is blank/null.

Maybe you could change your code to take account of this, & to check that the DMin() function is working as you intended:

d = DMin("ReminderDate", "MecirahIncomeDetailsT", "ReminderDate<=#" & Date & "# And ReminderDate>#00:00:00#")

BFN,

fp.
0
 
LVL 10

Assisted Solution

by:joriszwaenepoel
joriszwaenepoel earned 125 total points
ID: 39644193
00:00:00 is the default value for a Date variable.

I guess there are no records matching your criterium, so NULL is returned by the DMIN-function, and that NULL is then converted to the default value for a date-datatype.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 39644196
DMin() and DMax() ignore NULLs in the referenced field, but they will return a NULL if no records match or the recordsource is empty.

 You code should be:

Dim varRetuen as Variant

varReturn = DMin("ReminderDate", "MecirahIncomeDetailsT", "ReminderDate<=#" & Date & "#")
MsgBox vaReturn

If Not IsNull(varReturn) then
If varReturn <= Date Then


Jim.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bill201
ID: 39644197
fp:
thanks alot for your comment

in most  records the reminderdate field is null (there is date value on the reminder field just if i need to remind something)

the solution isn't fixing the problem and i get still the date value 00:00:00
0
 
LVL 57
ID: 39644200
BTW, a simpler way to write that logic is:

d = NZ(DMin("ReminderDate", "MecirahIncomeDetailsT", "ReminderDate<=#" & Date()  & "#"),Date())

Jim.
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 125 total points
ID: 39644293
You're welcome.  Thank you for your further explanation of your data.

Without having sight of this directly, I can only add to what other contributors have offered so far with...

d = DMin("ReminderDate", "MecirahIncomeDetailsT", "ReminderDate<=#" & Date & "# And Not(IsNull(ReminderDate))")

Jim's latest suggestion should be adequate for your needs though, I think.
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 125 total points
ID: 39644770
> the solution isn't fixing the problem and I get still the date value 00:00:00

That's because it is the default value for that data type in VBA. It can't hold Null as in SQL.
If you need that, declare as a Variant

Dim d As Variant

Then, whenever assigning a value, do something like this:

If IsNull(v) Or IsDate(v) Then
  d = v
End If

/gustav
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39644979
:)

Removing the 'On Error Resume Next' line may also help understand the issue as gustav succinctly summarised.
0
 

Author Comment

by:bill201
ID: 39646697
Many thanks to all of you, I was not here a good few hours, so I did not see your comments so I ignored until now, I am very sorry about this. Anyway thanks to you wonderful people I found the solution. And I split the points between you all, you deserve a lot more of these points, but it's what I  have :)

this the code that i write and it's working excellent - A combination of some answers
d = DMin("ReminderDate", "MecirahIncomeDetailsT", "ReminderDate<=#" & Date & "# And Not(IsNull(ReminderDate))")
If Not IsNull(d) Then MsgBox d

Open in new window

0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39647265
:)

We're just glad you found a solution.  No need to apologise at all.

Good luck with the rest of your project.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question