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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/