If . . . Then . . . Else statement (date)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I'm trying to do is have the Quarter field automatically update when the
Date Last Bid Sent info is entered. Date Last Bid Sent is in short date
format. Here is the code I've entered:

Private Sub Quarter_AfterUpdate()
If [Date_Last_Bid_Sent]("m") > 0 Then
[Quarter] = 1
ElseIf [Date_Last_Bid_Sent]("m") > 3 Then
[Quarter] = 2
ElseIf [Date_Last_Bid_Sent]("m") > 6 Then
[Quarter] = 3
ElseIf [Date_Last_Bid_Sent]("m") > 9 Then
[Quarter] = 4
End If

End Sub

But nothing happens. I have data already entered in the form, so I want this
code to be effective for any new record entered. Also, when I change the date
in one of the previous records, I want the quarter field to automatically
change. What am I doing wrong?
 
I,
First, never store a value in a field that can be derived from data
already saved. Since you are saving Date_Last_Bid_Sent, there's no need to
save Quarter. You can always re-derive the Quarter from that. Quarter can
be a unbound calculated field that always "displays" the correct value.
In Quarter, place this calculation in the ControlSource...

=IIf(Month([Date_Last_Bid_Sent])>0 And
Month([Date_Last_Bid_Sent])<4,1,IIf(Month([Date_Last_Bid_Sent])>3 And
Month([Date_Last_Bid_Sent])<7,2,IIf(Month([Date_Last_Bid_Sent])>6 And
Month([Date_Last_Bid_Sent])<10,3,4)))

Every time Date_Last_Bid_Sent is entered or edited, Quarter will display
the correct Value.
 
Your syntax is not correct. All you really need is the DatePart Functon:
[Quarter] = DatePart("q", [Date_Last_Bid])
The DatePart function will return the quarter the date is in, so the If Then
Else is not necessary. [Date_Last_Bid] must be a date/time data type.

Put the above code in the After Update event of the control you enter the
[Date_Last_Bid]. That will make it work for new and existing records.
 
I typed that in, but I keep getting this error:

Compile error:
Expected: line number or label or statement or end of statement

and the cursor would highlight the equal sign. But if I delete the =, then I
get this error message:

Compile error:
Expectd: =

HELP!

Al Camp said:
I,
First, never store a value in a field that can be derived from data
already saved. Since you are saving Date_Last_Bid_Sent, there's no need to
save Quarter. You can always re-derive the Quarter from that. Quarter can
be a unbound calculated field that always "displays" the correct value.
In Quarter, place this calculation in the ControlSource...

=IIf(Month([Date_Last_Bid_Sent])>0 And
Month([Date_Last_Bid_Sent])<4,1,IIf(Month([Date_Last_Bid_Sent])>3 And
Month([Date_Last_Bid_Sent])<7,2,IIf(Month([Date_Last_Bid_Sent])>6 And
Month([Date_Last_Bid_Sent])<10,3,4)))

Every time Date_Last_Bid_Sent is entered or edited, Quarter will display
the correct Value.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I.Candi said:
What I'm trying to do is have the Quarter field automatically update when
the
Date Last Bid Sent info is entered. Date Last Bid Sent is in short date
format. Here is the code I've entered:

Private Sub Quarter_AfterUpdate()
If []("m") > 0 Then
[Quarter] = 1
ElseIf [Date_Last_Bid_Sent]("m") > 3 Then
[Quarter] = 2
ElseIf [Date_Last_Bid_Sent]("m") > 6 Then
[Quarter] = 3
ElseIf [Date_Last_Bid_Sent]("m") > 9 Then
[Quarter] = 4
End If

End Sub

But nothing happens. I have data already entered in the form, so I want
this
code to be effective for any new record entered. Also, when I change the
date
in one of the previous records, I want the quarter field to automatically
change. What am I doing wrong?
Click to expand...
Click to expand...
 
Bless you. You saved another monitor from being pitched out of a 29th floor
window.

Thanks

Klatuu said:
Your syntax is not correct. All you really need is the DatePart Functon:
[Quarter] = DatePart("q", [Date_Last_Bid])
The DatePart function will return the quarter the date is in, so the If Then
Else is not necessary. [Date_Last_Bid] must be a date/time data type.

Put the above code in the After Update event of the control you enter the
[Date_Last_Bid]. That will make it work for new and existing records.


I.Candi said:
What I'm trying to do is have the Quarter field automatically update when the
Date Last Bid Sent info is entered. Date Last Bid Sent is in short date
format. Here is the code I've entered:

Private Sub Quarter_AfterUpdate()
If [Date_Last_Bid_Sent]("m") > 0 Then
[Quarter] = 1
ElseIf [Date_Last_Bid_Sent]("m") > 3 Then
[Quarter] = 2
ElseIf [Date_Last_Bid_Sent]("m") > 6 Then
[Quarter] = 3
ElseIf [Date_Last_Bid_Sent]("m") > 9 Then
[Quarter] = 4
End If

End Sub

But nothing happens. I have data already entered in the form, so I want this
code to be effective for any new record entered. Also, when I change the date
in one of the previous records, I want the quarter field to automatically
change. What am I doing wrong?
Click to expand...
Click to expand...
 
Thanks for your help Mr. Camp. I got the answer.

Al Camp said:
I,
First, never store a value in a field that can be derived from data
already saved. Since you are saving Date_Last_Bid_Sent, there's no need to
save Quarter. You can always re-derive the Quarter from that. Quarter can
be a unbound calculated field that always "displays" the correct value.
In Quarter, place this calculation in the ControlSource...

=IIf(Month([Date_Last_Bid_Sent])>0 And
Month([Date_Last_Bid_Sent])<4,1,IIf(Month([Date_Last_Bid_Sent])>3 And
Month([Date_Last_Bid_Sent])<7,2,IIf(Month([Date_Last_Bid_Sent])>6 And
Month([Date_Last_Bid_Sent])<10,3,4)))

Every time Date_Last_Bid_Sent is entered or edited, Quarter will display
the correct Value.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I.Candi said:
What I'm trying to do is have the Quarter field automatically update when
the
Date Last Bid Sent info is entered. Date Last Bid Sent is in short date
format. Here is the code I've entered:

Private Sub Quarter_AfterUpdate()
If []("m") > 0 Then
[Quarter] = 1
ElseIf [Date_Last_Bid_Sent]("m") > 3 Then
[Quarter] = 2
ElseIf [Date_Last_Bid_Sent]("m") > 6 Then
[Quarter] = 3
ElseIf [Date_Last_Bid_Sent]("m") > 9 Then
[Quarter] = 4
End If

End Sub

But nothing happens. I have data already entered in the form, so I want
this
code to be effective for any new record entered. Also, when I change the
date
in one of the previous records, I want the quarter field to automatically
change. What am I doing wrong?
Click to expand...
Click to expand...
 
Candi,
Sounds like your entering that IIF into the form module? It needs to go
into field Quarter as the ControlSource.
Actauly, Kaatu's solution is much simpler...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I.Candi said:
Thanks for your help Mr. Camp. I got the answer.

Al Camp said:
I,
First, never store a value in a field that can be derived from data
already saved. Since you are saving Date_Last_Bid_Sent, there's no need
to
save Quarter. You can always re-derive the Quarter from that. Quarter
can
be a unbound calculated field that always "displays" the correct value.
In Quarter, place this calculation in the ControlSource...

=IIf(Month([Date_Last_Bid_Sent])>0 And
Month([Date_Last_Bid_Sent])<4,1,IIf(Month([Date_Last_Bid_Sent])>3 And
Month([Date_Last_Bid_Sent])<7,2,IIf(Month([Date_Last_Bid_Sent])>6 And
Month([Date_Last_Bid_Sent])<10,3,4)))

Every time Date_Last_Bid_Sent is entered or edited, Quarter will
display
the correct Value.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I.Candi said:
What I'm trying to do is have the Quarter field automatically update
when
the
Date Last Bid Sent info is entered. Date Last Bid Sent is in short date
format. Here is the code I've entered:

Private Sub Quarter_AfterUpdate()
If []("m") > 0 Then
[Quarter] = 1
ElseIf [Date_Last_Bid_Sent]("m") > 3 Then
[Quarter] = 2
ElseIf [Date_Last_Bid_Sent]("m") > 6 Then
[Quarter] = 3
ElseIf [Date_Last_Bid_Sent]("m") > 9 Then
[Quarter] = 4
End If

End Sub

But nothing happens. I have data already entered in the form, so I want
this
code to be effective for any new record entered. Also, when I change
the
date
in one of the previous records, I want the quarter field to
automatically
change. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report/Query not selecting specific records 6
Count Records for this week, this month, this quarter and this yea 2
Enter quarter automaticly based on date entered 4
checking for duplicates 1
date into current and previous quarters 3
Form to fill data on multiple queries? 1
If cell = now – current Qtr – 28 days then RED 3
Quarterly inputs 2

Back
Top