IIf statement with dates

[Infinite]

I was wondering how to get this statement to work

IIF(Start/date="year(2012)",yes,no)

I have tried but all it does is say all yes, or all no.
My table has the correct format of date/time, I tried 2013, 2014, but still the same thing. If that doesn't/does does Access have excels own version of "Like" or/and "Contains?" Thanks!:banghead:

 

[Brianwarnock]

Iif (year([start/date])= 2012,"yes","no")

 

[Infinite]

Thanks a TON man! That worked like a charm! Thanks again!

 

[CJ_London]

dates are a numeric type, but typically formatted as text.

Not sure of the context of where you are using this - and since you have not simply copied your code, it is difficult to say but perhaps this is what you are looking for

IIF(year([Start/date])=2012,yes,no)

I also strongly recommend you avoid using spaces and non alpha numeric characters in your naming - it will only come back to bite you in the future.

ah - beaten by Brian!
Note that Yes and No will present as -1 and 0 as a query result, if you want the words, you need to use "yes","No"

 

[Infinite]

Well I wasnt going to use the words "yes" and "no" I was going to turn this into another formula, but I have that all worked out. Now, back to one of my questions, does access of a "Like" and/or "Contains" in it? Thanks once again!

 

[Brianwarnock]

Access does have a Like function but it is advised to only use it on text data and definitely not dates.
Not sure of the context for contains , access does have the In function and there is also Instr.

Brian

 

[Infinite]

Ok, this is excatly what I wanted to know, now how would I also add it for lots, like I have the start date of 2012, and for that year the IRSs gas price was $.50 a mile, but I have dates for 2013 and 2014. How would I do that using only 1 table?

 

[Mile-O]

Something like:

Format([DateField, "yyyymmdd") Like [Enter Year] & "*"

 

[Infinite]

Well, im not sure what that is for. Is that just a instance were I would use the "Like" function? If so, it doesn't look like that is what I would need then. Thanks anyways though.

 

[Infinite]

IIf(Year([startdate])=2012,[Miles one way]*.55.5,IIf(Year([StartDate])=2013,[Miles one way]*56.5,IIf(Year([StartDate])=2014,[Miles one way]*.56,0)))

Thats what I got, but, the problem I have is, the price is fifty five and a half cents per mile, how would I wright that? .55.5? It errors at that. Thanks!

 

[Minty]

Use 0.555
But you would be better to build this with a table that held the yearly rates. You will have to nest an extra function every year which will get unmanageable very quickly...

 

[Infinite]

How would I do that? FYI, I am 15, so...not the smartest person here

 

[CJ_London]

dates are numeric so you would use, =,<,> or between (in a query, VBA does not have a between). Not sure you can use IN in this context, suggest you try it but I think you would have problems.

As Brian says, Like is a text function so you could use something like

iif(format([Start/Date],"mm/dd/yyyy") like "*2012",true, false)

but it is clumsy and will be slow because of the comparison and the fact that it won't be using indexing (anything with * at the beginning will do a sequential search)

 

[Infinite]

IIf(Year([startdate])=2012,[Miles one way]*0.555*2,IIf(Year([StartDate])=2013,[Miles one way]*0.565*2,IIf(Year([StartDate])=2014,[Miles one way]*0.56,0*2)))

Thats what I got. Should I do it different? And, if so, how? I am VERY new at all this.

 

[CJ_London]

you should not be hardcoding rates into your query, you should have a separate rate table - at it's simplest it would be

tblMileageRates
RateYear - integer, primary key
Rate - currency

then your query would be something like

SELECT StartDate, Rate, [Miles one way], [Miles one way]* 2 * Rate as Charge
FROM tblExpenses, tblMileageRates
WHERE Year(StartDate)=RateYear

 

[Infinite]

1st, im not using a query. Or is query just another word for function, table, etc, etc?

2nd, to make another table just for the mileage rates? That seems like a lot of work just for that, seeing is the IRS rates for gas mileage are never gonna change. What would be the point of another table? Why would you do that? Make it run better? To change it up if need be? Less code? I know im sounding like a complete moron like this, but...sad truth, again, im not the smartest bulb, sharpest knife, etc,etc Thanks!

 

[Brianwarnock]

You have different rates for different years in your IIF ?

Using a table enables you to add a further year and it's rates without changing the query code.

Brian

 

[Infinite]

ya

IIf(Year([startdate])=2012,[Miles one way]*0.555*2*[Vehicles],IIf(Year([StartDate])=2013,[Miles one way]*0.565*2*[Vehicles],IIf(Year([StartDate])=2014,[Miles one way]*0.56,0*2*[Vehicles])))

see?
But doing it your way would make it easier, but, lol, if I only have to add one more small code change each year, would make it easier, but, thats not long term at all. hmm, maybe adding another table would me better.

 

[Infinite]

Now that im thinking about it, sticking with my way might be a little better, seeing as the IRS only posts the gas prices each year, so I would have to add that each year, and adding some IIF coding isn't hard at all. So as what to do.

 

[Brianwarnock]

Hard coding values is not considered good practice as it can so easily come back to bite you, but if you have a simple project you may be ok.

My advice would be to do it the "correct" way everytime, that way it becomes natural.

Brian