Building Expression with 2 different field types & format stay the

[Guest]

Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?

 

[John Spencer (MVP)]

You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

 

[Guest]

Thank you very much!!!! I knew it was probably really simple but I haven't
worked on Access in about 12 months... just back into it... thanks again...
Happy New Year!

You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?

 

[Guest]

Hey John ~

I have another question, how would you add IIF(Is null) to this expression
(If Fair Market Value Offer field is empty then I want it to be null)

FMV: "FMV" & Format([Fair Market Value Offer], "currency") & " on " & [FMV
Offer Dt])

Heather

You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?

 

[John Spencer (MVP)]

I'm guessing you want to have NULL returned for the entire expression.
In VBA:
IIF (IsNull([Fair Market Value Offer]),Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

In SQL the following _MIGHT_ be a little more efficient, but probably not
noticeable in most situations.
IIF ([Fair Market Value Offer] Is Null,Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

Hey John ~

I have another question, how would you add IIF(Is null) to this expression
(If Fair Market Value Offer field is empty then I want it to be null)

FMV: "FMV" & Format([Fair Market Value Offer], "currency") & " on " & [FMV
Offer Dt])

Heather

You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?

 

[Guest]

Thank you so much John.... I can't tell you how much I appreciate all your help

I'm guessing you want to have NULL returned for the entire expression.
In VBA:
IIF (IsNull([Fair Market Value Offer]),Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

In SQL the following _MIGHT_ be a little more efficient, but probably not
noticeable in most situations.
IIF ([Fair Market Value Offer] Is Null,Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

Hey John ~

I have another question, how would you add IIF(Is null) to this expression
(If Fair Market Value Offer field is empty then I want it to be null)

FMV: "FMV" & Format([Fair Market Value Offer], "currency") & " on " & [FMV
Offer Dt])

Heather

You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

Heather Polasek wrote:

Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?

 

[Guest]

Thank you so much John... I really appreciate it!!!

I'm guessing you want to have NULL returned for the entire expression.
In VBA:
IIF (IsNull([Fair Market Value Offer]),Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

In SQL the following _MIGHT_ be a little more efficient, but probably not
noticeable in most situations.
IIF ([Fair Market Value Offer] Is Null,Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

Hey John ~

I have another question, how would you add IIF(Is null) to this expression
(If Fair Market Value Offer field is empty then I want it to be null)

FMV: "FMV" & Format([Fair Market Value Offer], "currency") & " on " & [FMV
Offer Dt])

Heather

You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

Heather Polasek wrote:

Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?