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

If your design specifications are to display all legitimate dates as mm/dd/yy and nulls as "N/A", I would just use the "raw" date field in a text box and set the Format property to:

mm/dd/yy;;;"N/A"

I wouldn't format anything in the query and would not render different formats in the query.

Duane Hookom MVP
MS Access

----------------------------------------
> From: bmorris.gm@gmail.com
>
> 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-----
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
>
> 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/