code for between dates

[Lori2836 via AccessMonster.com]

Good morning. I need a little help in trying to figure out how to write a
little code. We have a database that tracks all quotes. A quote is created,
engineering assigns a part number, then the costing department goes in and
enters the cost....and on down the line. These quotes are available even
after being closed out, so the labor rates have to stay the same. Each year,
the labor rates change and this is how it has been coded. I can't seem to
get the "between" to work. It was fine until the new rates came out. Then
I had to add between dates for "if [Date Initiated] between #5/17/2008# and
#4/16/2009# Then". I get a Compile error that says Expected: Then or Go To.
Can someone help? Or maybe there is a better way to write this?


Private Sub Hrs_Change()
If IsNumeric(Hrs.Text) Then
If [Date Initiated] < #5/16/2008# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 99.55 / 1000
End If

or

if [Date Initiated] between #5/17/2008# and #4/16/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 100.9 / 1000
End If

or


If [Date Initiated] > #4/17/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 108.6 / 1000
End If


Thanks,
Lori

 

[Software-Matters via AccessMonster.com]

I can't seem to

get the "between" to work. It was fine until the new rates came out. Then
I had to add between dates for "if [Date Initiated] between #5/17/2008# and
#4/16/2009# Then". I get a Compile error that says Expected: Then or Go To.

Does the error debug go to the between dates line?

try this instead:

if [Date Initiated] > #5/17/2008# and [Date Initiated] < #4/16/2009# Then

--
<a href="
http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>

 

[Software-Matters]

Does the error debug go to the between dates line?

try this instead:

if [Date Initiated] > #5/17/2008# and [Date Initiated] < #4/16/2009# Then

Regards
JD

 

[Lori2836 via AccessMonster.com]

I can't seem to
get the "between" to work. It was fine until the new rates came out. Then
I had to add between dates for "if [Date Initiated] between #5/17/2008# and
#4/16/2009# Then". I get a Compile error that says Expected: Then or Go To.

Does the error debug go to the between dates line?

try this instead:

if [Date Initiated] > #5/17/2008# and [Date Initiated] < #4/16/2009# Then

Perfect! Thank you. I tried writting it that way, I just didn't add the
second [Date Initiated]!
Thank you so much!

 

[John Spencer MVP]

Your codes should probably be more like the following

Private Sub Hrs_Change()
If IsNumeric(Hrs.Text) Then
If [Date Initiated] < #5/16/2008# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 99.55 / 1000


Elseif [Date Initiated] between #5/17/2008# and #4/16/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 100.9 / 1000


ElseIf [Date Initiated] > #4/17/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 108.6 / 1000
End If
End If 'IsNumeric(Hrs.Text)
End Sub

Although I would tend to have a table with date ranges and the factors in it.
Then life is a lot easier when new ranges are added.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Good morning. I need a little help in trying to figure out how to write a
little code. We have a database that tracks all quotes. A quote is created,
engineering assigns a part number, then the costing department goes in and
enters the cost....and on down the line. These quotes are available even
after being closed out, so the labor rates have to stay the same. Each year,
the labor rates change and this is how it has been coded. I can't seem to
get the "between" to work. It was fine until the new rates came out. Then
I had to add between dates for "if [Date Initiated] between #5/17/2008# and
#4/16/2009# Then". I get a Compile error that says Expected: Then or Go To.
Can someone help? Or maybe there is a better way to write this?


Private Sub Hrs_Change()
If IsNumeric(Hrs.Text) Then
If [Date Initiated] < #5/16/2008# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 99.55 / 1000
End If

or

if [Date Initiated] between #5/17/2008# and #4/16/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 100.9 / 1000
End If

or


If [Date Initiated] > #4/17/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 108.6 / 1000
End If


Thanks,
Lori

 

[Lori2836 via AccessMonster.com]

John - for some reason it won't take the word "between" - I changed the line
to read:

Elseif [Date Initiated] between #5/17/2008# and #4/16/2009# Then and it
automatically turned red.

Do you know why that would happen?

Your codes should probably be more like the following

Private Sub Hrs_Change()
If IsNumeric(Hrs.Text) Then
If [Date Initiated] < #5/16/2008# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 99.55 / 1000

Elseif [Date Initiated] between #5/17/2008# and #4/16/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 100.9 / 1000

ElseIf [Date Initiated] > #4/17/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 108.6 / 1000
End If
End If 'IsNumeric(Hrs.Text)
End Sub

Although I would tend to have a table with date ranges and the factors in it.
Then life is a lot easier when new ranges are added.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Good morning. I need a little help in trying to figure out how to write a
little code. We have a database that tracks all quotes. A quote is created,

[quoted text clipped - 40 lines]

Thanks,
Lori

 

[John Spencer]

Because BETWEEN is an SQL construct and is not available in VBA

Try

elseIf [date Initiated] >= #5/17/2008# and [date Initiated]
<=#4/16/2009# THEN




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John - for some reason it won't take the word "between" - I changed the line
to read:

Elseif [Date Initiated] between #5/17/2008# and #4/16/2009# Then and it
automatically turned red.

Do you know why that would happen?

Your codes should probably be more like the following

Private Sub Hrs_Change()
If IsNumeric(Hrs.Text) Then
If [Date Initiated] < #5/16/2008# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 99.55 / 1000

Elseif [Date Initiated] between #5/17/2008# and #4/16/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 100.9 / 1000

ElseIf [Date Initiated] > #4/17/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 108.6 / 1000
End If
End If 'IsNumeric(Hrs.Text)
End Sub

Although I would tend to have a table with date ranges and the factors in it.
Then life is a lot easier when new ranges are added.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Good morning. I need a little help in trying to figure out how to write a
little code. We have a database that tracks all quotes. A quote is created,

[quoted text clipped - 40 lines]

Thanks,
Lori

 

[Lori2836 via AccessMonster.com]

I made the change and it hasn't been rejected. But the qty columns are no
longer being calculated since the new formulas were created for the new year.
It worked great when the first section of code specified <#5/16/2008# and the
second section was specified as >#5/17/2008. When hours were entered into
the hours column, it automatically calculated each qty column. Now if I add
the hours, it does nothing. Any ideas?

Thanks for your help,
Lori

Because BETWEEN is an SQL construct and is not available in VBA

Try

elseIf [date Initiated] >= #5/17/2008# and [date Initiated]
<=#4/16/2009# THEN

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John - for some reason it won't take the word "between" - I changed the line
to read:

[quoted text clipped - 45 lines]

 

[JimBurke via AccessMonster.com]

I'm pretty sure that Between is an SQL keyword, not a VBA keyword.

John - for some reason it won't take the word "between" - I changed the line
to read:

Elseif [Date Initiated] between #5/17/2008# and #4/16/2009# Then and it
automatically turned red.

Do you know why that would happen?

Your codes should probably be more like the following

[quoted text clipped - 37 lines]

 

[JimBurke via AccessMonster.com]

Whoops, I missed Johns earlier reply saying that - sorry.

I'm pretty sure that Between is an SQL keyword, not a VBA keyword.

John - for some reason it won't take the word "between" - I changed the line
to read:

[quoted text clipped - 9 lines]

 

[Lori2836 via AccessMonster.com]

Thanks Jim - I removed the "between" as John suggested. It is no longer
rejecting the code. It just doesn't calculate as it is supposed to.

Lori

I'm pretty sure that Between is an SQL keyword, not a VBA keyword.

John - for some reason it won't take the word "between" - I changed the line
to read:

[quoted text clipped - 9 lines]

 

[JimBurke via AccessMonster.com]

Do you really want to set all five unit costs to the same value? Before
trying anything else, just want to make sure that is really what you want.
Offhand your code looks OK.

Thanks Jim - I removed the "between" as John suggested. It is no longer
rejecting the code. It just doesn't calculate as it is supposed to.

Lori

I'm pretty sure that Between is an SQL keyword, not a VBA keyword.

[quoted text clipped - 3 lines]

 

[JimBurke via AccessMonster.com]

Why don't you re-post your code that you have now. I was going by what John
had posted. You may have something coded differently.

Thanks Jim - I removed the "between" as John suggested. It is no longer
rejecting the code. It just doesn't calculate as it is supposed to.

Lori

I'm pretty sure that Between is an SQL keyword, not a VBA keyword.

[quoted text clipped - 3 lines]

 

[Lori2836 via AccessMonster.com]

Private Sub Hrs_Change()
If IsNumeric(Hrs.Text) Then
If [Date Initiated] < #5/16/2008# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 99.55 / 1000


ElseIf [Date Initiated] >= #5/17/2008# And [Date Initiated] <=
#4/9/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 100.9 / 1000


ElseIf [Date Initiated] > #4/10/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 108.6 / 1000

End If
End If
End Sub

Why don't you re-post your code that you have now. I was going by what John
had posted. You may have something coded differently.

Thanks Jim - I removed the "between" as John suggested. It is no longer
rejecting the code. It just doesn't calculate as it is supposed to.

[quoted text clipped - 6 lines]

 

[JimBurke via AccessMonster.com]

Looking at that code, it looks OK to me. The problem is, I know nothing about
your application. You made this statement earlier:

'But the qty columns are no
longer being calculated since the new formulas were created for the new year.
It worked great when the first section of code specified <#5/16/2008# and the
second section was specified as >#5/17/2008. When hours were entered into
the hours column, it automatically calculated each qty column. Now if I add
the hours, it does nothing.'

WHere do these hours get entered? Is there a form that is bound to a table?
Is hours one of the fields in that table? Where is this code, and how does it
get called?

Have you tried stepping through the code in debug mode? I'd do that before I
went any further. Set a breakpoint wherever you call the Hrs_Change sub, then
step through the code one line at a time. See what the values of hrs.text and
[Date Initiated} are, and see which statements are executed.

Private Sub Hrs_Change()
If IsNumeric(Hrs.Text) Then
If [Date Initiated] < #5/16/2008# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 99.55 / 1000


ElseIf [Date Initiated] >= #5/17/2008# And [Date Initiated] <=
#4/9/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 100.9 / 1000


ElseIf [Date Initiated] > #4/10/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 108.6 / 1000

End If
End If
End Sub

Why don't you re-post your code that you have now. I was going by what John
had posted. You may have something coded differently.

[quoted text clipped - 4 lines]

 

[JimBurke via AccessMonster.com]

WHoops, guess I missed the fact that your sub is called hrs_Change and that
you must be using the hrs field's change event - I never use the On Change
event, I always use BeforeUpdate or AfterUpdate. Still, set a breakpoint at
the first line and check the things I mentioned when the event gets triggered.

Private Sub Hrs_Change()
If IsNumeric(Hrs.Text) Then
If [Date Initiated] < #5/16/2008# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 99.55 / 1000


ElseIf [Date Initiated] >= #5/17/2008# And [Date Initiated] <=
#4/9/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 100.9 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 100.9 / 1000


ElseIf [Date Initiated] > #4/10/2009# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 108.6 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 108.6 / 1000

End If
End If
End Sub

Why don't you re-post your code that you have now. I was going by what John
had posted. You may have something coded differently.

[quoted text clipped - 4 lines]

 

[Lori2836 via AccessMonster.com]

Hi Jim. I think my problem is all of a sudden, the subform (where the cost
calculation is taking place) is no longer recognizing the Date Initiated,
which is entered in the "In-Process Face Sheets" table, which feeds the main
form. I've tried updating my code to say (below) but when I enter the hours
in the Hours column, I get a run time error of 2465 and it says it can't find
the field "|" referred to in my expression. The way the form works is the
cost estimator adds the hours its going to take to complete a certain
operation and the Cost columns calculate as below. I need the Date
Initiated field to be recognized in the subform, so it can decide which labor
rate to use.

If [In-Process Face Sheets]![Date Initiated] < #5/16/2008# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 99.55 / 1000


Thanks,
Lori

WHoops, guess I missed the fact that your sub is called hrs_Change and that
you must be using the hrs field's change event - I never use the On Change
event, I always use BeforeUpdate or AfterUpdate. Still, set a breakpoint at
the first line and check the things I mentioned when the event gets triggered.

Private Sub Hrs_Change()
If IsNumeric(Hrs.Text) Then

[quoted text clipped - 31 lines]

 

[Lori2836 via AccessMonster.com]

Never mind. I figured my problem out.....by changing [In-Process Face Sheets]
to Parent("Date Initiated")....it now works! Thanks for your help.

Lori

Hi Jim. I think my problem is all of a sudden, the subform (where the cost
calculation is taking place) is no longer recognizing the Date Initiated,
which is entered in the "In-Process Face Sheets" table, which feeds the main
form. I've tried updating my code to say (below) but when I enter the hours
in the Hours column, I get a run time error of 2465 and it says it can't find
the field "|" referred to in my expression. The way the form works is the
cost estimator adds the hours its going to take to complete a certain
operation and the Cost columns calculate as below. I need the Date
Initiated field to be recognized in the subform, so it can decide which labor
rate to use.

If [In-Process Face Sheets]![Date Initiated] < #5/16/2008# Then
Unit_Cost1.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost2.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost3.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost4.Value = CDbl(Hrs.Text) * 99.55 / 1000
Unit_Cost5.Value = CDbl(Hrs.Text) * 99.55 / 1000

Thanks,
Lori

WHoops, guess I missed the fact that your sub is called hrs_Change and that
you must be using the hrs field's change event - I never use the On Change

[quoted text clipped - 6 lines]