ACCESS FEED: RE: [MS_AccessPros] Do I understand this right??

Senin, 02 Desember 2013

RE: [MS_AccessPros] Do I understand this right??

Duane,
Thanks for looking...
The date is May 3, 2014 and displays on the report as 5/3/2014 (should be
05/03/14)
If the mdr has no date value, it should display N/A
In the db entry, the field [Site Visit] is a date field

On the report - data properties I have 6 fields for "_Site Visit" (which is
a Text Box in the Detail section)
Control Source: _Site Visit
Text Format: Plain Text
Running Sum: No
Input Mask:
Enabled: Yes
Smart Tag:

Are you suggesting I can use the input mask to display the date as 05/03/14
(or N/A if no date is set)?

In the original post, it appears to me that whoever was messing with the
query before me broke it as the format output is never used (nSiteVisit) and
the final output is just based upon the Nz line.

Thanks,
B

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: Monday, December 02, 2013 5:58 PM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] Do I understand this right??

I'm not sure if there is a problem or just a request to get a better
understanding.

The query might be easier to read using this syntax:

SELECT MDR.FY, MDR.Code, MDR.[Site Visit], MDR.[Site Visit Occured],
MDR.[Contract Canceled], MDR.[Project Type],
Format([Site Visit],"mm/dd/yy") AS nSiteVisit,
Nz([Site Visit],"N/A") AS [_Site Visit],
Nz([Contract Canceled],"N/A") AS [_Contract Canceled],
BFP.[Budget Amount], BFP.[Other Amount]
FROM [Master Data Repository] MDR, [Budget FY Projects Query] BFP
WHERE MDR.FY="14" AND MDR.[Project Type]="FY Projects";

Apparently [Site Visit] is a date field in one of the tables that the
developer wants to be able to display in a couple different formats. You can
create almost as many calculated columns as you wish based on the same
field.

If this is for a form or report, I would not create these three columns in
the query:

Format([Site Visit],"mm/dd/yy") AS nSiteVisit,
Nz([Site Visit],"N/A") AS [_Site Visit],
Nz([Contract Canceled],"N/A") AS [_Contract Canceled],

I would use the expressions in the Control Source properties of text boxes.

When you state "Final date output should be 05/03/14" it doesn't tell us
much. Which is the day value and which is the month? Are you not seeing this
in your query results?

Duane Hookom MVP
MS Access

________________________________
> From: bmorris.gm@gmail.com
>
> Hello – once again thanks for everyone's help and input.
>
> From an existing query by the original creator I have:
>
> SELECT [Master Data Repository].FY, [Master Data Repository].Code,
> [Master Data Repository].[Site Visit], [Master Data Repository].[Site
> Visit Occured], [Master Data Repository].[Contract Canceled], [Master
> Data Repository].[Project Type], Format([Site Visit],"mm/dd/yy") AS
> nSiteVisit, Nz([Site Visit],"N/A") AS [_Site Visit], Nz([Contract
> Canceled],"N/A") AS [_Contract Canceled], [Budget FY Projects
> Query].[Budget Amount], [Budget FY Projects Query].[Other Amount]
>
> FROM [Master Data Repository], [Budget FY Projects Query]
>
> WHERE ((([Master Data Repository].FY)="14") AND (([Master Data
> Repository].[Project Type])="FY Projects"));
>
> I understand most of what is done, but it appears that there is
> formatting done to the data and I'm not sure I understand the
> nomenclature, what I'm looking at is:
>
> SELECT
>
> 1) [Master Data Repository].[Site Visit],
>
> 2) Format([Site Visit],"mm/dd/yy") AS nSiteVisit,
>
> 3) Nz([Site Visit],"N/A") AS [_Site Visit],
>
> #1 pulls the site visit date from the DB
>
> #2 formats the date in the MM/DD/YY format
>
> #3 somehow makes the date "N/A" if there is no date in the site visit
field.
>
> The form displays the value for " _Site Visit"
>
> How does the date value get from #2 to #3? – or are they unrelated?
>
> What good is nSiteVisit?
>
> Final date output should be 05/03/14
>
> Has someone meddled and broke it before me?
>
> Can this be done in vba for the form by reading the "Site Visit",
> correcting the format and the output to "_Site Visit" (or is "_Site
> Visit" a field that cannot be written to from vba)?
>
> Thanks,
>
> B

------------------------------------

Yahoo Groups Links