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

 

[jamaarneen]

Thanks Pwise: Maybe it's simple, but I'm not sure I know how to use this KaluachCol library. I have set a reference, but I don't know how exactly to work with it in VBA ([blue]Dim kK As KaluachS [/blue]and [blue]Set kK = New KaluachS[/blue] gives me an error: "ActiveX component can't create object")

Thanks kcmass: I had a look at both
- at 'dafaweek' didn't found any functionality for the 'Parsha' (that's what I'm looking for)
- at 'greve.net' I had a look, and it seems to me far very too much code. I think I would do it myself more compact.

[red]But, back to the begin of the thread - my actual problem:[/red]
Imagine I want a list of all Months, between 01-Mar-2008 and 15-Aug-2011, regardless of the fact if I have this Month in My table 'sales' or not. How do I achieve this???

Thanks again for everybody
Ja

 

[PHV]

list of all Months, between 01-Mar-2008 and 15-Aug-2011
Create a table named, say, tblIntegers with a single numeric field named, say, I.
Then, as a starting point, create this query:

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT DateAdd('m',10*B.I+A.I,[StartDate])
FROM tblIntegers A, tblIntegers B
WHERE DateAdd('m',10*A.I+B.I,[StartDate])<=[EndDate]

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

 

[PWise]

try


Public k As KALUACHCOMLib.KaluachS

sub kk

Set k = New KALUACHCOMLib.KaluachS

k.Diaspora = 0

k.PutHebrewDate 25, 8, 5766
MsgBox k.GetCivilMonth & "\" & k.GetCivilDay & "\" & k.GetCivilYear

end sub

 

[jamaarneen]

Thanks PHV:
Your suggestion is - as usual - short and very powerful. but in this case, it seems - for me at least - complex too.
I experimented a bit, and I really don't understand 1) for what reason have you reversed the calculation in the WHERE clause [blue]'10*A.I+B.I' in place of '10*B.I+A.I'[/blue]. And also 2) what is the number 10 for?


Thanks PWise:
I have tried, and still the same "ActiveX component can't create object" error message.

 

[PHV]

it seems - for me at least - complex too
Well, the simplest way is to create a month table (fairly easy in excel).

 

[jamaarneen]

You're right PHV, but I would very appreciate some explanation.
It's always great to learn from experienced people

 

[PHV]

B = Tens, A = Units

 

[jamaarneen]

Thanks a lot PHV (for the first lesson... ) a star..

1) If I understand right, the 'units' and the 'Tens' are to create values until 99 (and so on is possible withe "C = Hundreds")
[red]sorry, in the begin i did'nt realised that the tblIntegre should have all single numerics)[/red]

2) i still don't understand, why you have put in the WHERE clause the opposite calculation. your code gave dates even higher then 'EndDate'.
for me this one worked perfect:

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT DateAdd('m',10*B.I+A.I,[StartDate]) AS Expr1, A.I, B.I
FROM tblIntegers AS A, tblIntegers AS B
WHERE (((DateAdd('m',[b]10*B.I+A.I[/b],[StartDate]))<=[EndDate]));

(allthough I got confused, why sometimes is access asking me twice for the parameters???)

Thanks in advance
(As you see, even a hint - might be a lesson... so it IS worth)

 

[PHV]

OOps, sorry for the typo in the WHERE clause :~/

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

 

[jamaarneen]

OK. I'm glad I got the lesson

The next step will be, how to combine this with some other functionality (there is a thread going on "Using Count to return 0 when no records returned". You can see there my reply)

And the second - and important thing - how to work this out with some other value - not dates where we have the 'AddDate' function.

I'll try, I hope for success (otherwise you might hear from me :-; )

 

[kcmass]

Your original problem/request. If you want Gregorian months between and two dates try the below. It just stuffs the Month names into an array. It then concatenates all the names and hands that back as a string. You could also stuff them in a table or, or, or.

Regards

Kevin


Sub Test()
Dim Answer As String
Answer = MonthsBetweenDates(#12/3/1989#, #3/1/2010#)
Debug.Print Answer
End Sub

Function MonthsBetweenDates(StartDate As Date, EndDate As Date)
Dim strMonthsArray() As String
Dim strMonths As String
Dim lngCounter As Long
Dim lngMonths As Long
Dim dteRollingDate As Date
'How many moths between dates
lngMonths = DateDiff("M", StartDate, EndDate)
'Size array to hold number of months
ReDim strMonthsArray(lngMonths)
dteRollingDate = StartDate
'Go through the months involved and place in array
For lngCounter = 0 To lngMonths - 1
strMonthsArray(lngCounter) = Format(dteRollingDate, "MMMM")
'Increment the date reference
dteRollingDate = DateAdd("M", 1, dteRollingDate)
Next
strMonths = ""
For lngCounter = 0 To lngMonths - 1
strMonths = strMonths & strMonthsArray(lngCounter) & ", "
Next
' get rid of last comma
MonthsBetweenDates = Left(strMonths, Len(strMonths) - 2)
End Function

 

[jamaarneen]

kcmass,

my specific problem is with Hebrew Months/weeks/..., but your your suggestion to work with an array, gives a nice idea.

I'm trying something more in the direction of generating a query - PHV's suggestion - because this might give me more possibility to work with some more data out of other tables/queries.