Query with if statement and value from form

beginner123

Registered User.
Local time
Today, 15:43
Joined
Apr 13, 2013
Messages
44
Hi,
Please go easy on me!
I have a query:
SELECT Calibration.KKS, Calibration.[Calibrated Date], Calibration.Frequency, DateAdd('m',[Frequency],[Calibrated Date]) AS [Next Calibration Due]
FROM Calibration
WHERE (((Calibration.KKS) Like "1*"))
ORDER BY Calibration.KKS;

I would like to do something like:
If option button 1 on form is true then
SELECT Calibration.KKS, Calibration.[Calibrated Date], Calibration.Frequency, DateAdd('m',[Frequency],[Calibrated Date]) AS [Next Calibration Due]
FROM Calibration
WHERE (((Calibration.KKS) Like "1*"))
ORDER BY Calibration.KKS;
Else
If option button 2 on form is true then
SELECT Calibration.KKS, Calibration.[Calibrated Date], Calibration.Frequency, DateAdd('m',[Frequency],[Calibrated Date]) AS [Next Calibration Due]
FROM Calibration
WHERE (((Calibration.KKS) Like "2*"))
ORDER BY Calibration.KKS;

Is this possible?
[forms]![Calibration Query]![Line1]

At present I have 4 queries running from different buttons
just having different like values, 0,1,2,3

Regards
Robin
 
This video covers the aspect of using an option group. However I do realise that you might not be able to ferret out the information, If proves to be the case let me know.

https://m.youtube.com/watch?v=mAjnip9kbvg
 
Put ... Like [forms]![Calibration Query]![Line1] & "*" ... into the query builder for the criteria. I think it will work.
 
Are they mutually exclusive option buttons, i.e. they can never occur at the same time? If they are then ions has got you covered, otherwise you'll need a different approach.
 
Many Thanks All,

They are not mutually exclusive option buttons, but this will be coded.
Like [forms]![Calibration Query]![Line1] & "*"
(Line1,Line2.Line3 etc are option buttons)
But Line1 is only on or off? So I would need if or case,

If Line1
WHERE (((Calibration.KKS) Like "1*"))

If Line2
WHERE (((Calibration.KKS) Like "2*"))

Uncle Gizmo

I have been watching your videos this morning and think that Video 7 is what I am looking at doing, Is it possible to get a copy of this so I can see if I can adapt the code?

Again many thanks and I appreciate your time

Regards
Robin
 
They can be mutually exclusive if you use a Frame then all you do is use ions code.
 
Hi All,

I have tryed using SQL to concatenate, but am receiving an error.
F string -

Any guidance would be great. Screen shot attached.
I am learning lots this weekend.

Thanks
Again
 

Attachments

  • error.jpg
    error.jpg
    83.8 KB · Views: 75
What's the point of having radio buttons if they aren't mutually exclusive?
 
HI,

They are now are mutually exclusive in a Frame as you suggested.
So you can only select one option.

With the screen dump attached I was trying to pass a value in to the SQL depending on what option was selected.

Many Thanks for your persistance with me.

Regards
Rob
 
Place this Code:-

MsgBox " >>> " & strFilterSQL


just above:-

Me.recordSource = strFilterSQL

Post the content of the message nox here.
 
Many Thanks Again,

From running SQL I can see that.
"SELECT Calibration.KKS,Calibration.Calibrated Date,Calibration.Frequency FROM Calibration"
Need to have
"SELECT Calibration.KKS,Calibration.[Calibrated Date],Calibration.Frequency FROM Calibration"

Which after a quick read are because it contains a name space.
Getting there slowly, :)
 

Attachments

  • error.jpg
    error.jpg
    39.2 KB · Views: 76
One last question if you guys have a couple of minutes.
My options now work as expected.

Attached is my latest error. Which I think is down to using a calculated field.
Calibrated Date + Frequency in days = Due Date

When I load the the form for the 1st time the due date shows correct.
When I use the Filter on Line I receive #Name? for the due date?

I need to keep due date as a calculated field as I want to filter on this as well finally.

If the calculated field is correct in the table why can't I display it when I apply my filter.

Many Thanks again for every ones support on helping me.
 

Attachments

  • error1.jpg
    error1.jpg
    42.6 KB · Views: 82

Users who are viewing this thread

Back
Top Bottom