VBA dlookup but with where

[Ruegen]

BookingMinimum = DLookup("MinimumAudience", "tblJncShowYear", "ShowsID=" & ShowsID)



I have a problem with this because once the show is selected - it picks the first value from the junction and not on the condition of the year selected.

i.e. selected year 2012

cascading combobox select show according to year

I need to import the price/minimum audience according to the show and the year the show is in....

It pulls in the wrong price/minimum audience at the moment. (I think it just pulls in the first year even if it is the second).

so I need it shows ID but also the year it is tied to.

[Ruegen]

Do I just use an AND in the criteria?

[Ruegen]

ShowPricePerStudent = DLookup("ShowCostTotal", "tblJncShowYear", "ShowsID=" & ShowsID And "ShowYearID=" & Me.BookingYear)

but I get a type missmatch....

[Ruegen]

ShowPricePerStudent = DLookup("ShowCostTotal", "tblJncShowYear", "ShowsID=" & Me.ShowsID & " AND ShowYearID=" & Me.BookingYear)

works

[ssanfu]

Slight change needed.....
If "Me.BookingYear" is a number, try:

ShowPricePerStudent = DLookup("ShowCostTotal", "tblJncShowYear", "ShowsID = " & ShowsID & " And ShowYearID = " & Me.BookingYear)

[Ruegen]

Slight change needed.....
If "Me.BookingYear" is a number, try:

ShowPricePerStudent = DLookup("ShowCostTotal", "tblJncShowYear", "ShowsID = " & ShowsID & " And ShowYearID = " & Me.BookingYear)

Thanks but curious, what exactly is the change compared to my previous post? I'm not being sarcastic I'm very worried now

[ssanfu]

Not a thing.
You solved it just before I posted the same thing.

[Ruegen]

Not a thing.
You solved it just before I posted the same thing.

oh phew