Inputbox on programming? :(

[radicrains]

Hey guys.. I need help on some programming... I need to query for soon to be overdue drawings... i understand that i can put:



"Between [enterdate] and dateAdd("d",14,[enterdate])"

The thing is, i need to find out the information on 3 different areas: Owner, Class & production.. Hence, i will have a total of 3 querys just so i could obtain information on these 3 fields...

I've tried to add them together and put it under the "or" function, but the outcome is empty...

So I've starting to write a program that helps me to combine all these 3 fields together, hoever, there's mistake that i can't seem to solve in this progra, and i hope you guys will help me out..


Here's the program i wrote:

Function CompareDate(ParamArray FieldArray() As Variant)
Dim J as Variant
Dim FirstDate As Variant
Dim IntervalType As String
Dim MyDate As Variant
Dim Number As Integer

IntervalType="d"
FirstDate= InputBox("EnterStartDate")
Number = InputBox ("ENTER NUMBER OF DAYS TO ADD")
MyDate = "NewDate: " & DateAdd(IntervalType,number,FirstDate)
MsgBox MyDate

If FieldArray(0) >= FirstDate And FieldArray(0) <= MyDate
Then J=FieldArray(0)
End If

CompareDate=J

End Function

Thanks guys...

[NoellaG]

Hi,

have you thought of using an unbound form with 3 text or comboboxes, depending on your database? The users can fill in the comboboxes on the form, and depending on the filled in values you can build the sql WHERE clause.

I usually start with something like

dim strSQL as string

strSQL = "1 = 1 "

if len(me.mytextbox1) > 0 then

strSQL = strSQL & " and myField1 = """ & me.mytextbox1 & """"

end if
if len(me.mytextbox2) > 0 then
.... and so on...

you can then use the SQL WHERE to complete the SQL string or use it as a filter expression.
gr
NG

[radicrains]

Hi NG,
If my understanding is correct, a combo/list box are drop down menus that allow us to choose the values.. However, that isn't what i need in my report...

Unless there is a way to do it that i don't know of, please explain it to me as I'm still new to Micrsoft Access.. The main thing that i need is to have the 3 different fields in 3 different query to be together in a report...

Eg, Query 1 Fields: DrawingNo, Title, DateDueToOwner LatestRevDate

Criteria:
*Under the DateDueToOwner Field* --- Between [EnterStartDate] and DateAdd("d",14,[EnterStartDate])
*Under the LatestRevDate Field* --- "Is Null"

Repeat same query for DateDueToClass & DateDueToProduction respectively..

Hence, the end report product will be..
DrawingNo,Title, DateDueToOwner, DateDueToClass, DateDueToProduction


The problem is, When I combine these 3 fields, The outcome is empty as the query changes.. That's is the reason why I'm trying to create an inputbox in a module so that the user will be able to key in the date required 1 time, and it query the information individually for each field..

Any suggestions? :P

thanks..

[NoellaG]

Hi,

draw a form with the necessary text box(es), let the users enter there values in that form and let the query criteria refer to the values on that form. So, you'll have someting like:

Between Forms![frmMyForm]![txtStartDate] and DateAdd("d",14,Forms![frmMyForm]![txtStartDate])

[radicrains]

HI,

Hmmmm, I've never tried using Forms before. So it's pretty complicated.. but if that's the way to do it, then i shall learn it that way... My Attchment workplace contains sensitive information, hence i'm not allowed to bring work home.. So I'll try out ur method tmr, first thing in the morning! I'll report back at ya at the outcome..

In the mean time, thank you so much for helping me this far..

[radicrains]

hi,

i've tried creating a form, a textbox for date input and a query.. the results that i got is the same as the normal parameter queries that i used; it's empty.. i think i kinda understood as to why the outcome is empty.. when i put them together in report, rather then having a usual of 1 query to 1 field.. it combines all the 3 query together..

eg:
rather than a ration of 1 query : 1 field, it has become.. query1 and query2 and query3 : 1 field.. hence the outcome is empty...

[NoellaG]

Hi,

can't you put 3 subreports on 1 report? So each query provides a seperate subreport?

gr
NG

[radicrains]

HEY NG!!

I've manage to did it!

I went on to queried it the usual way.. this time adding the "or" function in.. then, i went on to build a module and create a function that helps me to compare the "is null" value under the "latestRevDate"..






Thank you for being patient with me this far! really appreciate it..