I added Date$ to a query field and am getting shaky results

[MLH]

Sometimes it works and sometimes it crashes.

If I want "Today is " & Date$ & "." to appear
in a query field, why might it work sometimes
and not others?

Would I be better to call a FN? Say, something
like "Today is " & GetDateString() & "." ???
If 'Yes' - why is that?

[MLH]

Re: I added Date$ to a query field and am getting shaky results

I substituted Format$(Now()," Long Date") and
that seems to run OK. But so did Date$ at first.
Then it later puked when executed in my live
data copy. C any reason why this approach
would ever become an issue?

[lyle fairfield]

Re: I added Date$ to a query field and am getting shaky results

MLH <CRCI@NorthStat e.netwrote in news:7laa045ke4 12afe3mn516js3s l9dktcde1@
4ax.com:

Sometimes it works and sometimes it crashes.
>
If I want "Today is " & Date$ & "." to appear
in a query field, why might it work sometimes
and not others?

It's probably got itself confused with "What sign are you?"

[Chuck Grimsby]

Re: I added Date$ to a query field and am getting shaky results

On Tue, 15 Apr 2008 18:25:11 -0400, MLH <CRCI@NorthStat e.netwrote:

>Sometimes it works and sometimes it crashes.
>
>If I want "Today is " & Date$ & "." to appear
>in a query field, why might it work sometimes
>and not others?
>
>Would I be better to call a FN? Say, something
>like "Today is " & GetDateString() & "." ???
>If 'Yes' - why is that?

Date$ and Now() are two rather different functions, and both follow
different paths to get the system date and time settings back to
whatever is calling them.

Date$ doesn't "honor" the Windows date and time format settings for
example, while both the Date() and Now() functions will. Surprisingly
however, I've personally found that Now() is faster then Date$.
(Indeed, even Format$(Now()," mm/dd/yyyy") is faster then Date$. Your
milage however, may vary.)

The Date$ and Now() functions also return 2 different types of data.
Date$ returns a string, Now() returns a date. (Well, ok, it's a
Variant sub-type Date, but it's close enough to a date that Access
won't appear to care, while it will care if it has to handle a String
as if it was a date, for sorting, grouping, etc.)

As for using a module level function to get the date rather then the
Now() function (or Date() function for that matter), I've only had to
result to that when I've needed further processing on the information,
or in cases where I needed a date that was "universal" across many PCs
in multiple locations. For example, when I needed a date (and/or
time) that was going to be the same everywhere across an organization,
regardless of where the user was, or despite what they did to their
computer's clock.


--
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

[lyle]

Re: I added Date$ to a query field and am getting shaky results

On Apr 15, 6:32 pm, MLH <C...@NorthStat e.netwrote:

I substituted Format$(Now()," Long Date") and
that seems to run OK. But so did Date$ at first.
Then it later puked when executed in my live
data copy. C any reason why this approach
would ever become an issue?

A reason could be that there is some other object or variable named
Date; I suggest that your try qualifying Date$ as VBA.Date$. If you
want the result to be formatted as per Regional Settings you could use
VBA.CDate(VBA.D ate$).
I'd probably use Format$(VBA.CDa te(VBA.Date$), "mmmm\ d\,\ yyyy"); I
can't think of any situation where this would fail.

[CDMAPoster@fortunejames.com]

Re: I added Date$ to a query field and am getting shaky results

On Apr 15, 6:52 pm, lyle fairfield <lylef...@yah00 .cawrote:

MLH <C...@NorthStat e.netwrote in news:7laa045ke4 12afe3mn516js3s l9dktcde1@
4ax.com:
>

Sometimes it works and sometimes it crashes.

>

If I want "Today is " & Date$ & "." to appear
in a query field, why might it work sometimes
and not others?

>
It's probably got itself confused with "What sign are you?"

Nice line Lyle! Look out for 'negative' answers :-). We're just too
hyperliteral. It's an occupational hazard. Here's a shot in the
dark. I think the part about confusion might be on the right track.
Suppose the OP had Office 97 installed, then upgraded to a non-
Professional (i.e., sans Access 2K3) Office 2K3, or maybe even
installed Office 2K3 Professional and went back to A97 for some
reason. It seems that programs such as Excel and Access, which share
a lot of the same functions should be on the same page, VBA librarily
speaking. No references would show up as Missing, but there might be
problems in Access when the OP tries to use those shared functions
because of the VBA library mismatch, the current VBA library being the
one from Office 2K3. If your VBA qualifier doesn't solve the problem
perhaps the OP should look into the possibility of such a mismatch.
IIRC, and I'm not positive about this, there are some situations where
A97 can run along with, say Office 2K3, and others where it can't
unless the functions are called within a Visual Basic Editor
environment or possibly just within a true module. Finding a solution
possibly depends on whether or not the Office install process deleted
the old VBA file, perhaps VBEEXT1.OLB, or not. Selecting a different
install directory for each version of Office on a single computer
should have kept the old VBA file intact. If so, clicking 'Browse' in
References to add a different type library reference as 'Visual Basic
For Applications' pointing to the old VBA file might solve the problem
in Access without breaking VBA in the rest of Office.

James A. Fortune
CDMAPoster@Fort uneJames.com

[CDMAPoster@fortunejames.com]

Re: I added Date$ to a query field and am getting shaky results

On Apr 15, 11:01 pm, CDMAPos...@fort unejames.com wrote:

On Apr 15, 6:52 pm, lyle fairfield <lylef...@yah00 .cawrote:

It's probably got itself confused with "What sign are you?"

>
Nice line Lyle!  Look out for 'negative' answers :-).  We're just too
hyperliteral.  It's an occupational hazard.  Here's a shot in the
dark.  I think the part about confusion might be on the right track.

:-)

A spry 70-year-old software developer decided to marry a 42-year-old
redhead. He explained to her that because he wakes easily at the
slightest movement they would always have to sleep in separate rooms
-- he could easily afford that arrangement for their honeymoon suite
-- but that they could still enjoy the privileges of marriage before
settling down for the night. On their wedding night he told her that
he'd take a little nap and asked her to be ready to consummate their
marriage. A little later she heard a few taps on her door. They
consummated their marriage and the software developer went back to his
room. A little later, he knocked on the door again. Delighted, his
new bride had no objection to celebrating their marriage again.
Afterward, he went back to his room again. A little while later,
there were more taps at the door. Astonished, she exclaimed, "Three
times in one night for a 70-year-old man!" His eyes widened as he
said, "You mean I've been here before!"

James A. Fortune
CDMAPoster@Fort uneJames.com

[CDMAPoster@fortunejames.com]

Re: I added Date$ to a query field and am getting shaky results

On Apr 15, 11:01 pm, CDMAPos...@fort unejames.com wrote:

If your VBA qualifier doesn't solve the problem
perhaps the OP should look into the possibility of such a mismatch.

If your software is designed to work on multiple versions of Access
you might be able to get away with unchecking the DAO 3.5 reference
placed there by the Access 97 install, then checking the DAO 3.6
reference left there by an earlier install of a later Office. YMMV.

James A. Fortune
CDMAPoster@Fort uneJames.com

[Larry Linson]

Re: I added Date$ to a query field and am getting shaky results


"MLH" <CRCI@NorthStat e.netwrote in message
news:05ba04d408 qursf1vggm5e0ac 4eid9uc19@4ax.c om...

>I substituted Format$(Now()," Long Date") and
that seems to run OK. But so did Date$ at first.
Then it later puked when executed in my live
data copy. C any reason why this approach
would ever become an issue?

Date is an Access reserved word (the name of a built-in function) and there
often are some problems in using reserved words for other purposes. The $
implies text, by the way, and date/time fields and variables are not text.

Larry Linson
Microsoft Office Access MVP