Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 974
  • Last Modified:

get a date value with dmin in access 2013

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
bill201
Asked:
bill201
  • 4
  • 2
  • 2
  • +2
4 Solutions
 
[ fanpages ]IT Services ConsultantCommented:
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
 
joriszwaenepoelCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President Online Computer Svcs, WNY IncCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
bill201Author Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President Online Computer Svcs, WNY IncCommented:
BTW, a simpler way to write that logic is:

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

Jim.
0
 
[ fanpages ]IT Services ConsultantCommented:
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
 
Gustav BrockCIOCommented:
> 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
 
[ fanpages ]IT Services ConsultantCommented:
:)

Removing the 'On Error Resume Next' line may also help understand the issue as gustav succinctly summarised.
0
 
bill201Author Commented:
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
 
[ fanpages ]IT Services ConsultantCommented:
:)

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

Good luck with the rest of your project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now