Auto insert number if year is

[Infinite]

Hello! I have a tblGasCost for the cost of gas, and I also have how many miles they travel, how many vehicles, and the date. I have a ID for the tblGasCost. Example. the Van traveled once, on 2/12/2012 to a show that was 20 miles away. The IRS cost per gallon for that year was $0.555, so in the tblShowCosts (the table with how many vehicles went to the show, and the miles traveled) I I have a field named GCGasID (that same field is in tblGasCost and is a auto number) and I put in the number 1 (because thats the ID number for 2012) and it then in a query it takes those numbers to get me the total costs. What I want is for access to auto fill the field GCGasID if it has the year. Thank you!

 

[burrina]

Here is the basics, of course substitute with your field names and criteria.

If  IsDate (Me.SomeDate) Then
Me.GCasID = Me.Somevalue
End If

HTH

 

[Infinite]

Err....not sure how/were to use it. In a field using the builder? Or a query. Pretty sure it isn't a query. And what do I place in Me.SomeDate, Me.Somevalue and IsDate?



Also, my fields for dates are ShowYear and it is a

ShowYear: Format([StartDate],"yyyy")

That is my field name, and as you can see in the code, its formated so its only the start date and the year. I might change it so its "mm-yyyy" so I can have more leeway if the prices change.

FYI: I am trying to NOT use a IIf sentence, seeing as if I had to have one for each in, in 20 years, I wouldn't be able to keep track. Unless that is a different iif from what im thinking?

 

[Infinite]

@burrina

I tried that, but it was really confusing me, so I just changed the GasID to the date, and I have the user just enter the date of the year if there going to be doing anything with the gas prices. Thanks for you help though!

 

[Infinite]

In case any one ever cares (me included) I found a new way to do it.

When a user creates a show, they enter the show name, start date, and end date. Then, on the last page, the click Finish. And that buttons does

Private Sub Command30_Click()



If Format([StartDate], "yyyy") = 2015 Then
Me.Text40.Value = 2015

ElseIf Format([StartDate], "yyyy") = 2014 Then
Me.Text40.Value = 2014

ElseIf Format([StartDate], "yyyy") = 2013 Then
Me.Text40.Value = 2013

ElseIf Format([StartDate], "yyyy") = 2012 Then
Me.Text40.Value = 2012

ElseIf Format([StartDate], "yyyy") = 2016 Then
Me.Text40.Value = 2016


DoCmd.Close acForm, "frmNewShow"




End If
End Sub

Yes, I would have to change the code each year, but in 2 minutes, I could have every year till for ever. Now, is there a way to do this better? Thanks!

Hope this helps some one!

 

[Infinite]

Never mind. Just found a way so simple, I cant believe I never found it out before.

Private Sub Command30_Click()

Me.TextBox.Value = Format([StartDate], "yyyy")

End Sub

That is such a simple way, I feel kinda stupid for not figuring it sooner.