Better Way?

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

Guest

I have a form that has the following fields of interrest: "Model_Year",
"Tonnage" and "Rate".
Model_Year is a number based off a previous form field entry frm_Quote_Info
i.e. "2005", Tonnage is entered on the current form frm_Cost_Sheet and I want
Rate automatically entered on the current form frm_Cost_Sheet based on
Model_Year and Tonnage.

I have a reference table tbl_Tonnage_Rate that has the fields Model_Year,
Tonnage_Start, Tonnage_Finish and Rate.
Sample entries:
Model Yr Mach Size Mach Size Rate
Start Finish
2004 501 700 $61.23
2004 701 1000 $90.33
2005 50 80 $28.03
2005 85 140 $29.40
2005 150 200 $30.78
2005 210 300 $39.38
2005 310 390 $43.68
2005 400 500 $54.08
2005 501 700 $62.45
2005 701 1000 $92.13
2006 50 80 $28.59
2006 85 140 $29.99

This is what I am trying to do, when the Tonnage is entered VB looks at the
Model_Year in the form to match to the Model_Year in the reference table
tbl_Tonnage_Rate and then look at the Tonnage entered to see where it falls
between the Mach Size Start and Finish and then return the Rate Value into
the form in field Rate.

It seems so easy in thought but to write code I am lost how to make 2
criterias before the "Then" section of an If-Then statement.

Thanks!!
Stacey
 
On Mon, 25 Apr 2005 08:51:01 -0700, "SMac"

(snip)
It seems so easy in thought but to write code I am lost how to make 2
criterias before the "Then" section of an If-Then statement.

Thanks!!
Stacey
Click to expand...

Im not sure why you need an If,Then for this, I would think its a
straightforward Dlookup

To answer the question:

If (Condition1=True) and (condition2 = True) then Goto ...

it could also be "or"
 
How would I write the DLookup?
Thanks!

1 said:
On Mon, 25 Apr 2005 08:51:01 -0700, "SMac"

(snip)

Im not sure why you need an If,Then for this, I would think its a
straightforward Dlookup

To answer the question:

If (Condition1=True) and (condition2 = True) then Goto ...

it could also be "or"
Click to expand...
 
How would I write the DLookup?
Thanks!
Click to expand...

I really don't know your problem well emough (or have time to) write
your code for you.

dlookup is complex, look it up in VB help there are several examples

hth
 
SMac said:
How would I write the DLookup?
Thanks!

:
Click to expand...
This is air code, but it will be something like this.

Dim MyRate As Currency
Dim StrCriteria as String

StrCriteria="Model_Year =" & Me!Model_Year
StrCriteria = StrCriteria & " Tonnage_Start <=" & Me!Rate
StrCriteria = strCriteria & " And Tonnage_Finish >=" & Me!Rate
If IsNull(DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria) Then
MyRate = 0
Else
MyRate = DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria)
EndIF

Me!Rate and Me!Model_Year are the control names on the form.

Ron
 
Thanks for the time!

One question, when I complie I get "Compile Error: Syntax error" on line
If IsNull(DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria) Then

Do you know why?

Thanks again!
Stacey

Ronald W. Roberts said:
SMac said:
How would I write the DLookup?
Thanks!

:
Click to expand...
This is air code, but it will be something like this.

Dim MyRate As Currency
Dim StrCriteria as String

StrCriteria="Model_Year =" & Me!Model_Year
StrCriteria = StrCriteria & " Tonnage_Start <=" & Me!Rate
StrCriteria = strCriteria & " And Tonnage_Finish >=" & Me!Rate
If IsNull(DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria) Then
MyRate = 0
Else
MyRate = DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria)
EndIF

Me!Rate and Me!Model_Year are the control names on the form.

Ron
Click to expand...
 
SMac said:
Thanks for the time!

One question, when I complie I get "Compile Error: Syntax error" on
line If IsNull(DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria)
Then

Do you know why?

Thanks again!
Stacey
Click to expand...

There's a missing closing parenthesis. Should be:

If IsNull(DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria)) Then

Note that the above should all be on one line, though it may have been
wrapped by the newsreader by the time you see it.
 
I figured out the syntax error but I do still have a question.
Model_Year and Tonnage_Start and Finish are Numbers, doesn't that change
some of the coding?

Thanks again!
Stacey

Ronald W. Roberts said:
SMac said:
How would I write the DLookup?
Thanks!

:
Click to expand...
This is air code, but it will be something like this.

Dim MyRate As Currency
Dim StrCriteria as String

StrCriteria="Model_Year =" & Me!Model_Year
StrCriteria = StrCriteria & " Tonnage_Start <=" & Me!Rate
StrCriteria = strCriteria & " And Tonnage_Finish >=" & Me!Rate
If IsNull(DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria) Then
MyRate = 0
Else
MyRate = DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria)
EndIF

Me!Rate and Me!Model_Year are the control names on the form.

Ron
Click to expand...
 
SMac said:
I figured out the syntax error but I do still have a question.
Model_Year and Tonnage_Start and Finish are Numbers, doesn't that
change some of the coding?

Thanks again!
Stacey

Ronald W. Roberts said:
This is air code, but it will be something like this.

Dim MyRate As Currency
Dim StrCriteria as String

StrCriteria="Model_Year =" & Me!Model_Year
StrCriteria = StrCriteria & " Tonnage_Start <=" & Me!Rate
StrCriteria = strCriteria & " And Tonnage_Finish >=" & Me!Rate
If IsNull(DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria) Then
MyRate = 0
Else
MyRate = DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria)
EndIF

Me!Rate and Me!Model_Year are the control names on the form.
Click to expand...
Click to expand...

I looks to me like Ron's suggested code does assume they are numbers, so
the coding wouldn't have to change for that. Is it working?
 
I get the following error:
Run-time error 3075
Syntax error (missing operator) in query expression 'Model_Year = 2005
Tonnage_Start <=55 And Tonnage _Finish>=55'.

The Model_Year is correct, 55 is what I put in for my tonnage and it needs
to fall in between or equal to the Start and Finish.

Please help.

Thanks!
Stacey

Dirk Goldgar said:
SMac said:
I figured out the syntax error but I do still have a question.
Model_Year and Tonnage_Start and Finish are Numbers, doesn't that
change some of the coding?

Thanks again!
Stacey

Ronald W. Roberts said:
This is air code, but it will be something like this.

Dim MyRate As Currency
Dim StrCriteria as String

StrCriteria="Model_Year =" & Me!Model_Year
StrCriteria = StrCriteria & " Tonnage_Start <=" & Me!Rate
StrCriteria = strCriteria & " And Tonnage_Finish >=" & Me!Rate
If IsNull(DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria) Then
MyRate = 0
Else
MyRate = DLookup("[Rate]", "tbl_Tonnage_Rate", StrCriteria)
EndIF

Me!Rate and Me!Model_Year are the control names on the form.
Click to expand...
Click to expand...

I looks to me like Ron's suggested code does assume they are numbers, so
the coding wouldn't have to change for that. Is it working?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
SMac said:
I get the following error:
Run-time error 3075
Syntax error (missing operator) in query expression 'Model_Year = 2005
Tonnage_Start <=55 And Tonnage _Finish>=55'.

The Model_Year is correct, 55 is what I put in for my tonnage and it
needs to fall in between or equal to the Start and Finish.

Please help.

Thanks!
Stacey
Click to expand...

Looks like the word "And' is missing from the code that builds
strCriteria As posted, is says:

It should be:

StrCriteria="Model_Year =" & Me!Model_Year
StrCriteria = StrCriteria & " And Tonnage_Start <=" & Me!Rate
StrCriteria = strCriteria & " And Tonnage_Finish >=" & Me!Rate
 
That was it! Works like a charm.
Thanks!

Dirk Goldgar said:
Looks like the word "And' is missing from the code that builds
strCriteria As posted, is says:


It should be:

StrCriteria="Model_Year =" & Me!Model_Year
StrCriteria = StrCriteria & " And Tonnage_Start <=" & Me!Rate
StrCriteria = strCriteria & " And Tonnage_Finish >=" & Me!Rate

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top