UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Date Format For Declared Variable, Access 2007    
 
   
nmartin1230
post Dec 4 2018, 10:14 AM
Post#1



Posts: 464
Joined: 20-May 14



I am trying to write some code that when a certain form opens in a DB it checks for the newest date in tblHolidays and if that date is 30 or less away the user gets a prompt to add more dates. My problem is that the variable shows as 12:00:00 AM and I want to format it as mm/dd/yyyy. My code works and I even have the variable in the msgbox prompt and it shows the proper date but when I step through the code the variable shows as the time. I've many different options that I can think of to no avail. He is the code the way I have it at the moment. Any help would be appreciated!

QUOTE
Private Sub Form_Load()

Dim Answer As Date
Answer = Format(Answer, "mm/dd/yyyy")


Answer = Format(DMax("Holiday", "tblHolidays"), "yyyy/mm/dd")

If Answer < Date + 700 Then

Select Case MsgBox("The last Holiday Date is " & (Answer) & " and is less then 30 days away." & vbCrLf & "Please add more days to the Holidays table", vbOKCancel, "Holidays")
Case vbOK:
DoCmd.OpenForm "Holidays", acNormal, "", "", , acNormal
End Select



End If
End Sub
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2018, 10:35 AM
Post#2


UA Admin
Posts: 34,092
Joined: 20-June 02
From: Newcastle, WA


Hi, there are few problems here.

First. Format() CONVERTS the date to a string. That's how the Format() function works, so right off the bat, you're losing the "dateness" of the variable by creating a string that "looks like a date".

Second. I'm not at all clear why you are comparing the variable to 700. What would that represent?

Also. You start out by giving an uninitialized variable a value which that same uninitialized variable, but formatted as a string. Why?

CODE
Answer = Format(Answer, "mm/dd/yyyy")


Here's where things start to take shape, though:

Answer = Format(DMax("Holiday", "tblHolidays"), "yyyy/mm/dd")

You have dimmed Answer as a date, but assigned to it a string using Format.


I might do this a little different knowing that I want to avoid the problem of string to date comparisons.
CODE
Private Sub Form_Load()

Dim Answer As Date

Answer = DMax("Holiday", "tblHolidays")

If Answer < DateAdd("d", 30, Date) Then
    Select Case MsgBox("The last Holiday Date is " & Format(Answer, "yyyy/mm/dd") & " and is less then 30 days away." & vbCrLf & "Please add more days to the Holidays table", vbOKCancel, "Holidays")
    Case vbOK
        DoCmd.OpenForm "Holidays", acNormal, "", "", , acNormal
    Case Else
    End Select
End If
End Sub

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
nmartin1230
post Dec 4 2018, 10:56 AM
Post#3



Posts: 464
Joined: 20-May 14



Grover,
A lot of that was just me playing around trying to see if I could get something to work. I didn't change the coded back to what I originally had before posting it on here(my fault). The 700 I put in there to make the msgbox popup due to the must future date in the table was 12/25/2020. Just trying to make sure the msgbox worked. Once, I got it to work I would've changed it back to 30. I will give your suggestion a try and report back. Thanks as always for the quick response!!!
Go to the top of the page
 
nmartin1230
post Dec 4 2018, 11:02 AM
Post#4



Posts: 464
Joined: 20-May 14



Okay, that works but I have one question. Does it matter that when I set a breakpoint on the "Answer=" line and hover over "Answer" it still shows 12:00:00 AM? I see that is still seeing the most future date correctly but could/would be better to get the variable to be the date?
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2018, 11:13 AM
Post#5


UA Admin
Posts: 34,092
Joined: 20-June 02
From: Newcastle, WA


It's not relevant until AFTER you retrieve the holiday date you want from the holiday table. It's being initialized when you dim it, but without a valid date assignment, it defaults to day/time 0.

But once you DO assign it with the DMax() function, hovering the cursor over it should show the retrieved date.

George

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
nmartin1230
post Dec 4 2018, 11:18 AM
Post#6



Posts: 464
Joined: 20-May 14



Gotcha! One other quick question. Is it possible to get the corresponding field from the table to show in the msgbox so the user knows what exact holiday it is? Again, thank you so much!
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2018, 02:25 PM
Post#7


UA Admin
Posts: 34,092
Joined: 20-June 02
From: Newcastle, WA


Yes. I assume you mean the name of the holiday on that date?

Create a variable for that, use the DLookup() function to get it, using the date retrieved.

strHoliday = DLookup("HolidayName", "tblHolidays", "tblHolidays.Holiday = " & Answer )

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
nmartin1230
post Dec 4 2018, 03:04 PM
Post#8



Posts: 464
Joined: 20-May 14



Okay, gave that a try and it is coming back null. What I actually have is

strHoliday=DLookup("Description","tblHolidays","Holidays.Holiday = " & Answer)


Go to the top of the page
 
Dan Dungan
post Dec 4 2018, 03:48 PM
Post#9



Posts: 280
Joined: 20-July 10
From: chatsworth, ca


George had tblHolidays.Holiday, but you have Holidays.Holiday. This might give you null results.
This post has been edited by Dan Dungan: Dec 4 2018, 03:49 PM

--------------------
Dan
Go to the top of the page
 
tina t
post Dec 4 2018, 05:21 PM
Post#10



Posts: 5,611
Joined: 11-November 10
From: SoCal, USA


and in fact, in this case, you don't need to preface the field name with the table name, in the criteria argument of the domain function - the table name is already supplied. so the following should work, as

strHoliday=DLookup("Description","tblHolidays","Holiday = " & Answer)

your table name, and field names must be correct for your database, of course, not "dummy" or "fill in" names.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2018, 05:43 PM
Post#11


UA Admin
Posts: 34,092
Joined: 20-June 02
From: Newcastle, WA


My apologies. I misnamed the table

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
nmartin1230
post Dec 6 2018, 12:48 PM
Post#12



Posts: 464
Joined: 20-May 14



Grover,

I caught that when I put it in the DB. The string still comes back null.

Tina,

I tried your suggestion but it also comes back as null.

This is what I am putting in the immediate window

?DLookup("Desc", "tblHolidays", "Holiday = " & Answer)
This post has been edited by nmartin1230: Dec 6 2018, 12:49 PM
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2018, 12:58 PM
Post#13


UA Admin
Posts: 34,092
Joined: 20-June 02
From: Newcastle, WA


The only way to know what is happening here is to find out what value is being passed to the DLookup in the variable Answer.

Do you know how to set breakpoints and step through the code?

George

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
nmartin1230
post Dec 6 2018, 01:05 PM
Post#14



Posts: 464
Joined: 20-May 14



Yes, I have done that and I get an error stating invalid use of Null. Now say I replace the Holiday with the ID field of the table and set it to equal a number it pulls up the holiday name but soon as I and the "& Answer" to the statement it errors out. Disregard the quotes around it as I do not have it the statement.
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2018, 01:16 PM
Post#15


UA Admin
Posts: 34,092
Joined: 20-June 02
From: Newcastle, WA


That probably means you are not properly assigning a value to Answer.

How is that being done again?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
nmartin1230
post Dec 6 2018, 01:25 PM
Post#16



Posts: 464
Joined: 20-May 14



I've kept everything the same and just added

Dim strDesc as String

strDesc = DLookup("Desc", "Holidays", "Holiday = " & Answer)

As I step through and I hover over Answer it shows the value as 12/25/2020 which is correct.
This post has been edited by nmartin1230: Dec 6 2018, 01:26 PM
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2018, 01:32 PM
Post#17


UA Admin
Posts: 34,092
Joined: 20-June 02
From: Newcastle, WA


Okay, so we need to compare a DATE to a DATE. Try this.

strDesc = DLookup("Desc", "Holidays", "Holiday = #" & Answer & "#")

By including the date delimiter, you should make Access recognize it as a date, not a simple math calculation.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
nmartin1230
post Dec 6 2018, 01:37 PM
Post#18



Posts: 464
Joined: 20-May 14



Well, that worked and I could have sworn I tried (in the many attempts)that but I guess I didn't. Thanks, again, Grover!
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2018, 03:25 PM
Post#19


UA Admin
Posts: 34,092
Joined: 20-June 02
From: Newcastle, WA


Sorry, for some reason I had it in my mind you were looking for a "holiday" like "Thanksgiving", not the calendar date.

Congratulations on solving the problem.

Continued success with the project.

George

...

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2018 - 06:15 PM