Generating a query - not based on a table 1

[jamaarneen]

Hi

I would like to be able to generate a Query (kind of) by VBA-code, but based on values not in a table.
by exp: to get a list of Months between to given dates (wich will given by the user).

Is there something like this? I think it should be something very simple, but I'm missing something important
Thanks
Ja

 

[dhookom]

I don't think there is any method for creating a select query in Access without using a table. Why not create a table of months?

Duane
Hook'D on Access
MS Access MVP

 

[jamaarneen]

Thanks Duane

The thing is,1) I thought that it is not such a good idea to keep a table for data that could be generated during run-time; 2) since i have actually several kind of 'lists' (mostly based on dates), it beginning to be more then one table.

But what do you think about working with a make-table query, which can be created during run-time based on parameters given by the user, and when done - to delete it.

would like to hear any opinion on that

Thanks
Ja

 

[dhookom]

Create a simple table. Maybe it could be just the numbers 1-10 or whatever. This is much more efficient and easier than creating and deleting a table. It is a good thing. I'm not sure why you would think it's not.

Duane
Hook'D on Access
MS Access MVP

 

[r937]

i agree, the integers table is a good thing

search within tek-tips and you will find several threads where the integers table is used for very similar functions, e.g. generating a range of dates

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jamaarneen]

Thanks both of you.
I'll try to figure out something, how to make use of an integer table for my purpose

 

[MichaelRed]

while I agree w/ dhookom[/cplor], I also think it would be interestinf / useful to know the use, propose (or intent) of the result.

There are numerous ways to generate a list of elements (months?) in Ms Access, and the how you wold use the list may offer clues to alternate approaches.




MichaelRed

 

[jamaarneen]

OK, I'll try to give you an idea.

I'm working on something that needs to work with Hebrew Calender. which is - in case you don't know - total different then the Gregorian calender.
I have found an ActiveX control which will do the converting. That means, if I'll enter a 'date', I'll get as result a Hebrew date (being tree integers: year, month and day).

Now I have to add some functionality. there is in the Hebrew calender 'names' for every week during a year, and since the amount of weeks are not equal each year (and some other factors), I have to develop some function to calculate each year the week-names.

it would be sloppy to just make tables for that, because we can never know for which year the user will like to have a week-list.

So tha idea is, when the user will fill in some parameters, VBA should be able to return a list of values. Or it may just be a 'sort by weeks', and so on.

I'm also intending to use my Hebrew-date-function in various queries

Thanks for your attention.
Ja

 

[MichaelRed]

so, if I understand it, given an arbitrary date, you want to retun the Hebrew date (available vis the already found ActiveX control), but also the week name of that that date?

Possibly an elaboration of that, such as the week name for a set of dates (up to and including an entire Hebrew (Gregorian?) calendar.

If the above, then a list of the possible (Hebrew Calendar) week names and some relationship of the names to some available value is necessary.



MichaelRed

 

[jamaarneen]

Right. of course a "list of the possible (Hebrew Calendar) week names and some relationship of the names to some available value is necessary".
But it isn't that simple (and that's why it is challenging...). And I'll to elaborate it in a few steps:
1) First I have to determine a Hebrew year. [red](by exp. the first day of the current Hebrew year - 5770 - was 19/09/2009, and the last day will be 8/09/2010.)[/red]
2) Now with should be able of creating a list with weeks: week number 1; week 2 ...
3)So in theory it would be that week 1 = NameA; week 2 = NameB and so on. But there are some times weeks without a name (sounds strange, but that's a matter of fact); and on the other hand, there could be weeks with two names [red]like: "NameX NameY"[/red]. these exceptions are different one year of another, what I want to write a function for.
4) One of the most common things the user will want to know, is the "week-name" for a given day(s) [red]exp.: "Monday NameX" is a very common way of representing a day in the hebrew calender)[/red]

And to the point: let's say I have a list of all the names [red]a table, or an 'enum'[/red], I still have to be able to take one certain day - or a range of days - , and attache to every day [red](more specific: to each seven days)[/red] a week-name, after running the function for it.

Hope it made more understandable - not the opposite...

Thanks for your time and advise
ja

 

[MichaelRed]

a bit of clarity, but no "answer'.

what / where is the list of wek names and the relationship(s)?

All else might follow (mayhap depending on the complexity)



MichaelRed

 

[PWise]

have a look @ kaluach.org or kaluach.net they hav a com object that dose excactly this can return
Parchas Matis-masi or evan
shbos chazon
shobos nacmoo

i once bulit a access db around this will lok for this

it would be sloppy to just make tables for that, because we can never know for which year the user will like to have a week-list.

I disagree with this any that can be put in a table should be put in a table

I evan started working on how many "Aliyos" can be given each week
7+maftir+x amount of hosofios ect

 

[jamaarneen]

PWise thanks for your advise

Yes, that's the kind of thing I'm looking for! I had a quick look at kalluach, but didn't see something to work with in VBA. (I have sent them a mail)

I am not sure I understand your disagree:

any that can be put in a table should be put in a table

I thought that "anything that can be calculated, should [red]not [/red]be put in a table"...

Ja

 

[jamaarneen]

MichaelRed:

The only data I got right now, is a table with two columns:
ParshaID
ParshaName
('parsha' is how a week name is called)
but as mentioned above, each year can have one or more 'leap weeks', and some weeks will be double-named.

Thanks, Ja

 

[PWise]

look around for
KaluachCOMdemoVB.zip

and KaluachCOM.dll
if you need any help let me know i will help you bli neder

 

[jamaarneen]

Thanks PWise.
There is something I'm not sure about it: I thought that 'dll' (like other objects for VB) is not usable for VBA-Access?
because I'm currently working with VBA - not VB
Thanks Ja

(If you want, you can reach me at 'my-tek-tip-name' at yahoo)

 

[PWise]

in VBA See
Menu-->Tools-->References

Set a References to KaluachCOM.dll

 

[kcmass]

Edit

 

[kcmass]

This points to a set of PHP functions for all sorts of Jewish calendar Routines with full source code. While you are not programming in PHP, the logic would be parallel to these.

http://www.ziporah-greve.net/prog/jewish-php.html#leapyear

 

[kcmass]

Another set of functions but this is said to run in VB6 or VBA.

http://www.dafaweek.com/HebCal/HebCalVb6.html