Can I use variables in a query? 2

[InkyRich]

Hello,
I want to be able to use two global variables in the criteria row of a select query e.g. Between DateBegin And DateEnd.
Is this possible and if so what is the correct syntax?

Hope you can help.

Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[PHV]

Create two public functions in a standard code module:

Public Function getDateBegin()
getDateBegin = DateBegin
End Function

Public Function getDateEnd()
getDateEnd = DateEnd
End Function

Now, the SQL criteria:
Between getDateBegin() And getDateEnd()

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[InkyRich]

Brilliant phv,
Seems that I am always saying thanks to you.
Have a star.

Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[InkyRich]

Hi all and phv,
Its been a long time since this reply was posted but I have just come back to my problem and discovered that this is not working.
I have the query set up as suggested with two functions GetDateBegin and GetDateEnd these are linked to two variables DateBegin and DateEnd which are dates in the '30/09/2008' format (Ihave tried '09/30/2008' format doesn't seem to make any difference), but the query is still not running properly.
In the criteria row I have the suggested SQL 'Between GetDateBegin() And GetDateEnd()'calling up the two functions.
Any suggestions as to why this does not work please? This is now quite urgent.

Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[poporacer]

Inky,
How is it not working and can you post your query and possibly a little info on the table structure to give us a little insight as to wha thte problem might be?

 

[InkyRich]

Hi poporacer,
Thanks for your reply.
The 'entry' comes from a combo selection box where the first date is entered the second date is then generated with VBA code and is 6 days on from the first date, thereby covering a week from the first date to the second. These two dates are variables DateBegin and DateEnd which are globals.
There are two functions, GetDateBegin and GetDateEnd. These pick up the two variables when the query QRYSelectWeekReport is activated. The code in the functions is as suggested by phv above.
The SQL 'Between GetDateBegin() And GetDateEnd()' is in the criteria row of the query sorting the column UnformattedDate in the table.
This should then sort the data and present the report just for the week selected - but it does not seem to work.
I have tried changing various things and using Debug.Print to look at values. The date values are getting right through to the functions correctly but are not working in the query. Any suggestions?

Thanks for your time on this.

Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[randy700]

How about...

Public Function getDateBegin() [COLOR=red]As Date[/color]
getDateBegin = DateBegin
End Function

Public Function getDateEnd() [COLOR=red]As Date[/color]
getDateEnd = DateEnd
End Function

Randy

 

[InkyRich]

Hi Randy,
Tried that but it makes no difference

Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[randy700]

What does Debug.Print show for the entire query?


Randy

 

[InkyRich]

Hi,
This is all very strange.
I have records in the table which are dated as follows:
14/06/2008
17/06/2008
18/06/2008
25/06/2008
29/09/2008
03/10/2008

the query runs fine for the june entries but when it comes to running it for the september and october entries it ignores them and gives me all of the june entries instead.
Strange

Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[missinglinq]

From your use of date format dd/mm/yy I'm going to guess that your regional settings are something other than USA. This presents a problem with SQL that Allen Browne discusses here along with workarounds:

http://www.allenbrowne.com/ser-36.html

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[jontout]

I have a similar piece of code, this works in Excel and should be fine in Access

sX = Mid(sDate, 4, 2)
eX = Mid(eDate, 4, 2)

SELECT RecNo, RiskDate, RiskCat, BusUnit, Dept, RiskGroupValue, RiskGroup, Status" _
FROM tblRiskLog
WHERE substring(RiskDate, 4, 2) >= '" & sX & "' " 
AND substring(RiskDate, 4, 2) <= '" & eX & "' "
ORDER BY RecNo

sX & eX are both dimmed as Strings, sDate & eDate are taken from an InputBox.


Any use?

 

[InkyRich]

Thanks chaps I will have a look and let you know on this.
Any more suggestions anyone?

Inky

Hampshire UK.

A fool and his money are soon parted - so you might as well send it to me!

 

[InkyRich]

Hi all again,
I still seem to have this problem but I have an update on my attempts at resolving this issue.
By leaving out the 'Between'predicate in the criteria row and just calling the getBeginDate function filters the correct begin date and likewise just calling the getEndDate function filters the correct end date. So that part is working and it is filtering, so could there be a fault in the 'Between' predicate? Is it possible that Between will not work with functions in the query?
Be interested to hear your responses.

Regards
Inky

Hampshire UK.

A fool and his money are soon parted - so you might as well send it to me!

 

[randy700]

Again.
Post the entire query and we may be able to help.


Randy

 

[InkyRich]

Hi randy700,
Thanks for getting back to me.
How do I paste a query?

Inky

Hampshire UK.

A fool and his money are soon parted - so you might as well send it to me!

 

[sxschech]

Open the query in design view and then click on View, SQL View. Then you can copy and paste the text.

 

[InkyRich]

Hi thanks for your help. Here is the SQL for the query:

SELECT TBLDayCheckLog.DayCheckLogID, TBLDayCheckLog.DateName, TBLDayCheckLog.DateToday, TBLDayCheckLog.UnformatDate, TBLDayCheckLog.ResEquCln, TBLDayCheckLog.ResEquClnComment, TBLDayCheckLog.CVEquCln, TBLDayCheckLog.CVEquClnComment, TBLDayCheckLog.StrMatCln, TBLDayCheckLog.StrMatClnComment, TBLDayCheckLog.EmerAlmTest, TBLDayCheckLog.EmerAlmTestComment, TBLDayCheckLog.RecepFirstAidKitAvail, TBLDayCheckLog.RecepFirstAidKitAvailComment, TBLDayCheckLog.PoolFirstAidKitAvail, TBLDayCheckLog.PoolFirstAidKitAvailComment, TBLDayCheckLog.PlantFirstAidKitAvail, TBLDayCheckLog.PlantFirstAidKitAvailComment, TBLDayCheckLog.FirstAidKit1Avail, TBLDayCheckLog.FirstAidKit1AvailComment, TBLDayCheckLog.FirstAidKit2Avail, TBLDayCheckLog.FirstAidKit2AvailComment, TBLDayCheckLog.GymClean, TBLDayCheckLog.GymCleanComment, TBLDayCheckLog.ChangRmClean, TBLDayCheckLog.ChangRmCleanComment, TBLDayCheckLog.PoolSideClean, TBLDayCheckLog.PoolSideCleanComment, TBLDayCheckLog.PlantClean, TBLDayCheckLog.PlantCleanComment, TBLDayCheckLog.SunBedClean, TBLDayCheckLog.SunBedCleanComment, TBLDayCheckLog.SteamRmClean, TBLDayCheckLog.SteamRmCleanComment, TBLDayCheckLog.SaunaClean, TBLDayCheckLog.SaunaCleanComment, TBLDayCheckLog.BeautyRmClean, TBLDayCheckLog.BeautyRmCleanComment, TBLDayCheckLog.RecepClean, TBLDayCheckLog.RecepCleanComment, TBLDayCheckLog.ChildPlayClean, TBLDayCheckLog.ChildPlayCleanComment, TBLDayCheckLog.StoreRmClean, TBLDayCheckLog.StoreRmCleanComment, TBLDayCheckLog.DanceStudClean, TBLDayCheckLog.DanceStudCleanComment, TBLDayCheckLog.Blank1, TBLDayCheckLog.Blank1Comment, TBLDayCheckLog.Blank2, TBLDayCheckLog.Blank2Comment, TBLDayCheckLog.Blank3, TBLDayCheckLog.Blank3Comment, TBLDayCheckLog.Name, TBLDayCheckLog.Date, TBLDayCheckLog.AgreeCheck
FROM TBLDayCheckLog
WHERE (((TBLDayCheckLog.UnformatDate) Between GetDateBegin() And GetDateEnd()));

If you prefer it in word format let me know.

Thanks

Inky

Hampshire UK.

A fool and his money are soon parted - so you might as well send it to me!

 

[randy700]

I see no reason your query should not work.

Here are a couple of things to check...

1. Are you sure both GetDateBegin and GetDateEnd have values?

2. Try forcing the # symbol around the dates. Such as
Between #09/01/08# and #09/30/08#


Randy

 

[InkyRich]

Hi Randy
Thanks for your reply.
Yes both variables are proved to have values.
Seccondly I cannot use the # symbols because I am calling up functions.

Ianky

Hampshire UK.

A fool and his money are soon parted - so you might as well send it to me!