Solved

get a date value with dmin in access 2013

Posted on 2013-11-13
10
805 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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:bill201
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
BTW, a simpler way to write that logic is:

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

Jim.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 125 total points
Comment Utility
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 49

Accepted Solution

by:
Gustav Brock earned 125 total points
Comment Utility
> 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 ]
Comment Utility
:)

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

Author Comment

by:bill201
Comment Utility
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 ]
Comment Utility
:)

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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 …

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now