Parameter query to show any records falling in on a date

[hockey8837]

Hi,
I've got a DB in which records have a start date and an end date.

I'd like to be able to run a parameter query that will prompt which month I'd like to look at, and return all records which fall into that month.

I haven't been able to figure out the right way to set this up, as I only get the start month records appearing (i.e. I'll want June, and only records with a June start date show up, rather than records that also start in May and end in August, etc., showing up, too).

My ultimate goal is to be able to look at a month-by-month report and see all records which fall into each month (yes, this will give duplicates, but that is okay).

I'm using Access 2007 and Windows XP.

Thanks!

 

[pbaldy]

If your data is structure as I suspect it is:

http://www.baldyweb.com/OverLap.htm

 

[hockey8837]

If your data is structure as I suspect it is:

http://www.baldyweb.com/OverLap.htm

Hi,
Yes, that is exactly what I want! I've had such a hard time finding any info on the web with this exact layout. I used your code as my base, but I'm a novice SQL, so I think this is what I want in the new module I created...

Option Compare Database
Public Function FlowersByMonth(strVariable As String, lngVariable As Long) As String
SELECT LatinName, StartMonth, EndMonth
FROM tblBloomTime
WHERE EndMonth <= Forms!frmFlowerList.EndMonth And EndMonth >= Forms!frmFlowerList.StartMonth

End Function

My 'tblBloomTime' stores the flower's ID#, and it's blooming start and end dates.

My form 'frmFlowerList' shows me all flowers in the DB and associated info, and it's based off of the query 'qryFlowersExtended'.

Now, 1.) Did I do this right? 2.) how do I get a report to show me the results of, say, all flowers in bloom in June? Basically, what's the next step?

I tried to call the function from a parameter query:

InBloom: FlowersByMonth([StartMonth],[EndMonth])

but it tells me "undefined function 'FlowersByMonth' in expression"

Thanks

 

[Brianwarnock]

That error usually occurs when your Function and module names are the same, they must be different, a module can have many functions in it.

But note also that you are comparing dates against months apparently.
Is that true if so what is the format of the month parameter, is it text or the month number?

Brian

 

[pbaldy]

As Brian noted, make sure the names aren't the same. That said, you can't just have SQL in a function like that; it will error. What does the data look like, and what are you expecting the function to return to the query?

 

[Brianwarnock]

I never read his function properly too busy with the other error, I do feel a twit for missing the obvious.

Brian

 

[hockey8837]

That error usually occurs when your Function and module names are the same, they must be different, a module can have many functions in it.

But note also that you are comparing dates against months apparently.
Is that true if so what is the format of the month parameter, is it text or the month number?

Brian

Hi,
I've gone back and forth with this DB trying to figure out the best way to track when a plant blooms, I really only need to know/show start month and end month. So, I settled on arbitrary dates, choosing the first day of each month of the year 2001 (i.e. January=1/1/01, Feb=2/1/01).

These dates are saved in the table 'tblMonths'

Then, I have in my 'frmFlowerDetails' a subform based off of the 'tblBloomTime' (the one that tracks Flower ID with bloom start and end date), and in this, the user will select the start of bloom month and end month from 2 combo boxes which have run SELECT queries for each to replace the date with the name of the month:

SELECT [tblMonths].ID, Format([Months],"mmmm") AS [Month Name], [tblMonths].Months, Format([Months],"m") AS [Month Number] FROM tblMonths;

My module is named basFlowersByMonth and my function is just FlowersByMonth.

I've attached a copy of the DB (minus the photos b/c it's too big otherwise)

Thanks!

 

[Brianwarnock]

I do not have 2007 so cannot handle your DB.
Pbaldy is better qualified than I and may come back and disagree with me, if so follow him.

I think that you have made life difficult for youself.
You cannnot compare, except for = on text months, April < January.

I would have stored numeric months in my table, and my Combos would have selected text but returned numeric, I think you can do that, if you did not want people to select numeric months.

Otherwise you are forced into convoluted conversions.

Brian

 

[hockey8837]

Hi,
Thanks, here's a copy of it in Access 02-03. I know my setup poor, I just have no idea how to better assign bloom periods. Originally, I had it setup on a numeric assignment (Jan=1, Feb=2, Mar=3, etc), but I changed it because I couldn't figure out how to arrange data in reports according to month if a flower is blooming over several months; i.e. when I select only the start and end bloom months on the input form, rather than every month it was blooming (as in check boxes-which I'd tried, too, but didn't know how to quantify the checks in numerical order for looking at them in reports), but, I didn't know how to get it to show any months in between.

So, I switched to a date setup, because I figured it would be easier to find help based on a calendar year with actual start and end dates. But, I'm still facing the same issue of looking at one month and seeing anything that is blooming at that time.

I'm happy to rearrange if it will solve my headache!
Thanks!

 

[Brianwarnock]

I'm sorry don't know when I'll get to this My wife's at Hospital all day tomorrow so today is busy with other things, maybe the weekend.

Brian

 

[hockey8837]

I'm sorry don't know when I'll get to this My wife's at Hospital all day tomorrow so today is busy with other things, maybe the weekend.

Brian

No problem at all! Hope everything is okay.
Thanks.

 

[Brianwarnock]

OK grabbed a few minutes not sure if what I have done is what you are after but changed 1 query it is suffixed by BJW, chaged the calculation for Bloomperiod as I thing yours failed the end of year test, added Bloommonths you will see it calls a function that is in Module1

Did not change any tables so handled the dates but would change the tables if it were my DB.

Not sure what you were after with the combo boxes mentioned is that to drive a query to list the plants that bloom during a given time period?

Brian

PS always compact your DB before zipping.

 

[pbaldy]

I hope your wife is doing okay Brian. Please give her our best wishes.

 

[hockey8837]

OK grabbed a few minutes not sure if what I have done is what you are after but changed 1 query it is suffixed by BJW, chaged the calculation for Bloomperiod as I thing yours failed the end of year test, added Bloommonths you will see it calls a function that is in Module1

Hi Brian,
Thanks so much. Looks like this is the start of where I need to be.
I copied your edited query bjwqryBloomTimeExtended, called it qryBloomTimeByMonth, and setup a parameter query from [bloommonths] to call any flowers that bloom in the month # entered, however, no matter what data I enter, this turns up a blank datasheet.

My plan was to use this parameter query to base a report off of to look at anything blooming in the month of "X".

Is this the incorrect way to pull data from this query? Would there be a better way to use the [bloommonths] data to select the month I'd like to view? I'm self-taught on access, so I'm still learning the best ways to do things.

Thanks a million for all of your help thus far!

 

[Brianwarnock]

The query as written would allow you to look for a plant and see its blooming months, you need to tackle the question you want slightly differently, but what I have done is based on that query. Owing to the fact that you cannot use aliases in where clauses I have had to use the Month function again , another reason to switch your start and end to months as it says and not a date.

This is the SQL for the query

SELECT Month([tblbloomtime].[StartMonth]) AS StartMonth, Month([tblbloomtime].[EndMonth]) AS EndMonth, tblBloomTime.AdditionalInfo, IIf(IsNull([LatinName]),[CommonName],[LatinName] & ", " & [CommonName]) AS FlowerName, tblFlowers.Height, tblFlowers.Type, IIf([EndMonth]-[StartMonth]+1>0,[EndMonth]-[StartMonth]+1,[EndMonth]-[StartMonth]+13) AS [BloomPeriod(mths)], bloommonths([startmonth],[endmonth]) AS bloommonths
FROM tblFlowers INNER JOIN tblBloomTime ON tblFlowers.ID = tblBloomTime.FlowersID
WHERE (((req(Month([Startmonth]),Month([endmonth]),[parm1],[parm2]))="required"));

You will be able to caopy and then paste this into the SqL view after selecting the tables in design view.
I have not used combos at this stage just parameter prompt , both need entering so if you only want April enter 4 for both. Obviously it needs a bit of polish on the names here.

copy and paste this function into Module1 with the other code

Public Function req(smth As Long, emth As Long, parm1 As Long, parm2 As Long) As String

If smth <= emth Then
    If parm2 >= smth And parm1 <= emth Then
    req = "required"
    Else: req = " "
    Exit Function
    End If

Else

If (parm2 >= smth And parm1 <= 12) Or _
    (parm2 >= 1 And parm1 <= emth) Then
    req = "required"
    Else: req = " "
    Exit Function
    End If
End If
End Function

Note BTW that the function parameters are positional not keyword and therefore do not need to be the same name as in the query, but you probably knew that.

TTFN and best of luck

Brian

 

[hockey8837]

Wow,
I can't thank you enough! Looks like it works perfectly!
Thanks, again!

 

[Brianwarnock]

I thought that I would have ago this evening so that you can spend Friday polishing it.

Glad i could assist, will check back over the weekend

Brian

 

[Brianwarnock]

Hi

I have done a bit more work on this, including making a correction to 1 of my functions.
The full information, I hope, is in the Word document.

Have fun

Brian

 

[Brianwarnock]

I hope your wife is doing okay Brian. Please give her our best wishes.

Thanks Paul, its a regular check on her condition, she has had a form of cancer for 17 years, she was given 3 to 10 so feels lucky and is going quite well still.

Brian

 

[vbaInet]

this could have worked too if you paste it into a module and call it or run it from the click event of a button.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("NameOfYourQuery")

strSQL = "SELECT LatinName, StartMonth, EndMonth FROM tblBloomTime WHERE EndMonth " _
            & "LIKE '##/' & [Enter a Start Month number] & '/##' AND EndMonth Like '##/' & [Enter an End Month number] & '/##'"
qdf.SQL = strSQL
DoCmd.SetWarnings False
DoCmd.OpenQuery "NameOfYourQuery", acViewNormal, acEdit
DoCmd.SetWarnings True

simply uses pattern matching, Regular Expressions i think it's called. (amended)