Is this statement correct?

[Mansoor Ahmad]

Dear All

Can somebody tell me wheather the following statemnt is correct or not in stWhere string

Dim myInd As String

myInd = Forms![Form2]!CmbCustomer

Select Case Me!frmHVAC
Case 1
stWhere = ("[REJECT DATE]Between [date from] And [to]") And (" [CUSTOMER]= '" & myInd & "'") Or ("[CUSTOMER] = '" & myInd & "'") Or ("[REJECT DATE]Between [date from] And [to]")
Case 2
.......

thanks

 

[namliam]

It not correct...

stWhere = "([REJECT DATE]Between [date from] And [to]) And ([CUSTOMER]= '" & myInd & "') Or ([CUSTOMER] = '" & myInd & "') Or ([REJECT DATE] Between [date from] And [to]) "

Would be "more" correct. Assuming [Date From] and [To] are to be paramaters or something.... Tho i fail to see the logic of the statement: 1 and 2 or 1 or 2 ??

Good luck

Regards

The Mailman

 

[Mansoor Ahmad]

Namliam

Thanks for being there again for me. I had put the same question in full detail earlier on but did not receive any reply. I have explained the logic behind it. There might be some better solution which I am not aware of.

The link of my other question is as follows

http://www.access-programmers.co.uk/forums/showthread.php?threadid=56555[/URL]

While I am here

Can you please explain or refer some link regarding the arrangement of brackets, ' and " in VB statements. They have always confused me.

Thanks

 

[namliam]

I can see why you didnt get a reply on the other thread, its unclear and question up on question. Even now i get lost...

And it dont explain why the strange where....
In keeping with my example 1 and 2 or 1 or 2 ??
1 1
1 2
1 3
2 1
2 2
2 3
Based on above you will retrieve
1 1
1 2
1 3
2 2
But i dont think that is what you want, further you will get the same with only 1 or 2

You will have to explain more about your logic and what you are trying to achieve if i am to help you.

Can you please explain or refer some link regarding the arrangement of brackets, ' and " in VB statements. They have always confused me.

Both Mile-O-Phile and myself have explained it numerous times. If you search the forum i am sure you will be able to find it.... eg I am to lazy to start it all over again ...

Regards

 

[Mansoor Ahmad]

Thanks again for your reply. I am sorry for not been able to explain my question properly. I try here again.

On a form I have got following objects

A combo box (CmbCustomer)

An option group with 4 options, Reject date, Analysis date, Rej code, Liabcat
On Reject date and Analysis date options I want the data to be filtered over a range of dates like Reject date from …. To....)

Three command buttons, each opens separate reports

What I want?
I want the Customer combo box and the option group to work with and without each other. For example

If I select a customer in combo box and do not select any option in option group and click on CmdReport (Command button that opens report) it should filter the report only for selected Customer.

If I do not select Customer and chose any option and click on CmdReport it should filter the report only for the option selected.

If I select a Customer in combo box and also select an option in option group it should filter data based on Customer and the option selected.

I hope this makes sense now.
Looking forward to your reply.

 

[namliam]

You should "conditionalize" your where building

I assume now that your [date from] and [to] are on the form?!

** DONT use spaces in names !! **

strWhere = ""
if not isnull([date from]) and not isnull([to]) then
    strWhere = strWhere & "[REJECT DATE] Between #"& [date from] & "# And #" & [to] & "# ") 
end if
if not isnull(me.CmbCustomer) then
    if strwhere <> "" then strWhere = strWhere & " AND "
    strWhere = strWhere & " [CUSTOMER]= '" & me.CmbCustomer & "' "
end if

Or something like that.... and repeat that for each option you want to have...

Regards

 

[Mansoor Ahmad]

Thank you very much for your reply. Sorry for being late in response. I was caught up in some other problems.

I have managed to solve the problem. First of all I put[date from] and [to] txtboxes on the form whic I did not have before.

Secondly to make things easier I have copies them on a tab control and transfered some functions to second page.

Thanks for your help