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

Senin, 02 Desember 2013

[MS_AccessPros] Do I understand this right??

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)?