[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

get a date value with dmin in access 2013

Posted on 2013-11-13
10
Medium Priority
?
916 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 500 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 58
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 500 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 52

Accepted Solution

by:
Gustav Brock earned 500 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Suggested Courses

656 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