Solved

get a date value with dmin in access 2013

Posted on 2013-11-13
10
840 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 ]
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 49

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will show you how to use shortcut menus in the Access run-time environment.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

786 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