Data MisMatch error 1

[vbahelp07]

I do not know why i am now getting a data mismatch error

DB_UDF_IMH_ARTIST is a chartext field

Data in it are as such:
ABC
ABC.DEF

OriginalArtist: IIf(Len([DB_UDF_IMH_ARTIST]=3),[DB_UDF_IMH_ARTIST],Null)

what is wrong with my syntax?

 

[CaptainD]

My guess would be the use of Null, try

OriginalArtist: IIf(Len([DB_UDF_IMH_ARTIST]=3),[DB_UDF_IMH_ARTIST],"")

 

[vbahelp07]

seriously i thought i tried that!

i was at my wits end!

thanks!

can you help me with another syntax question ...

i want the column to show only the results (sales) for when the transaction was between a date range and the release date of the items were in the same year.

so i have this so far ...

NetSales: IIf([SOTransDate] Between 1/1/2004 And 12/31/2004 And [ML_UDF_IMH_RELEASEDATE] Like "*04",[SOExtChargeAmount],0)

but all the data is coming up $0

 

[dhookom]

There was a mis-placed ) in your first posting
OriginalArtist: IIf(Len([DB_UDF_IMH_ARTIST][red])[/red]=3[yellow])[/yellow],[DB_UDF_IMH_ARTIST],Null)

You must delimit dates with #. 1/1/2004 is 1 divided by 1 divided by 2004 which is a very small number. Also, if [ML_UDF_IMH_RELEASEDATE] is a date then don't compare it to a string. If you want to find the year of a date field, use the Year() function.

Duane MS Access MVP

 

[vbahelp07]

wonder why it didn't beep at me indicating i was missing ")".

i could not see it, now i do. thank you for pointing the what seemed to be the obvious.


ah ... ok, i changed it to:

NetSales: Sum(IIf([SOTransDate] Between #1/1/2004# And #12/31/2004# And Format([ML_UDF_IMH_RELEASEDATE],"yy")="04",[SOExtChargeAmount],0))

it's beautiful! thanks again!

 

[dhookom]

I would probably not convert your date to a string in order to compare it with another string. It's a minor point but I would use:

NetSales: Sum(IIf([SOTransDate] Between #1/1/2004# And #12/31/2004# And Year[ML_UDF_IMH_RELEASEDATE])=2004,[SOExtChargeAmount],0))

Or possibly

NetSales: Sum(Abs([SOTransDate] Between #1/1/2004# And #12/31/2004# And Year([ML_UDF_IMH_RELEASEDATE])=2004)*[SOExtChargeAmount])

Duane MS Access MVP

 

[vbahelp07]

ah, ok. i did not know about the Year thing. the only way i knew how was what I showed.

the 2nd part, what is the *, doesn't that mean multiple??

 

[dhookom]

what is the *"? multiplication operator.

Duane MS Access MVP

 

[vbahelp07]

Duane,
btw - this did not work.

Year[ML_UDF_IMH_RELEASEDATE])=2004

so for now i went back to the Format([ML_UDF_IM_RELEASEDATE],"yy")="04")

 

[lespaul]

unless it's a copy and paste error, you're missing a paren in the YEAR function:

Year[COLOR=red]([/color][ML_UDF_IMH_RELEASEDATE])=2004

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com