Year: Val(Right([Paidfor],4)) is this code correct 1

[Panchovia]

This code should give you the right 4 digits of the string (Paidfor) (example: may 2004)but I get a data mismatch error in access 2003, does the function val right exist in access 2003.

if so what might cause this error.

Pancho

 

[dhookom]

Is the field actually text or is it a date field? Is it possible the field might be null?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Panchovia . . .

Try:

[blue]Val(Right([purple][b]Nz([/b][/purple][PaidFor], "0000"[purple][b])[/b][/purple], 4))[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Panchovia]

Hi aceman1

The field is a text field
Your are correct your code is the one ,

Below is a another code this should group all the info of the same client in the transaction file and bring up missing months and paid months
this work perfectly well in access 2000,
when I converted the query to access 2003 it works also
but when I input it manually in access 2003 is gives an error message code to complex I have checked everything
IIf([Transaction].[Number] Between Month(CDate(Left([Paidfor],3) & "/01")) And IIf(InStr([Paidfor],"/")>0,Month(CDate(Mid([Paidfor],5,3) & "/01")),Month(CDate(Left([Paidfor],3) & "/01"))),1,0)

paidfor is a text field

input for the paidfor is as follows mmm 2004

or mmm/mmm 2004

must the cdate instruction also be formatted to mmm in the code to bring up the correct months how.

 

[TheAceMan1]

Panchovia . . .

Hmmmmmmmmmm

Its easy to see that [blue][Transaction].[Number][/blue] is a number from 1 to 12. What does the data in [blue][Paidfor][/blue] look like and whats its datatype?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Panchovia]

The date type is Text here the operator fills in the months paid

example
jan 2004
feb/mar 2004

 

[TheAceMan1]

Panchovia . . .

I broke it all down and could'nt find any errors. Including parenthesis groups which are intact. Since its an [blue]complex error[/blue] you have two choices:
[ol][li]Prescribe another [blue]custom field[/blue] to handle the trailing [blue]IIf[/blue] and use that field in the first. In the query this field should come before the custom field containing the beginning [blue]IIf[/blue].

[blue][purple][b]Custom1[/b][/purple]:IIf(InStr([Paidfor],"/")>0,Month(CDate(Mid([Paidfor],5,3) & "/01")),Month(CDate(Left([Paidfor],3) & "/01")))

[b]Custom2[/b]:IIf([Transaction].[Number] Between Month(CDate(Left([Paidfor],3) & "/01")) And [[purple][b]Custom1[/b][/purple]] ,1,0)[/blue]

[/li]
[li]Make a custom function[/li][/ol]
Special Note: If either [blue][Paidfor][/blue] or [blue][Transaction].[Number][/blue] contain [blue]Nulls[/blue] you could easily throw [purple]type mismatch errors!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]