Replies

Thanks Hans.

Hans,

You always provide excellent and effecient solutions and I just can’t thank you enough. Sincerely.

I had to make a minor change to the code because txtStart and txtEnd didn’t change no matter the selection. In case anyone needs this in the future, the change I made is below.

Original:
Me.txtStart = DateSerial(Year(Date), 3 * i – 2, 1)
Me.txtEnd = DateSerial(Year(Date), 3 * i + 1, 0)
Change:
Me.txtStart = DateSerial(Year(Date), 3 * Lo – 2, 1)
Me.txtEnd = DateSerial(Year(Date), 3 * Hi + 1, 0)

Yes….right as always

Thanks again Hans…..

Now, can I ask yet another question, please?

Taking the scenerio I’ve outlined in this thread and on the code you’ve provided, is there an effecient way to determine the following:

Determine the FIRST Date of the first selected quarter (i) and the LAST date of the last selected quarter selected (i)? i.e. Let’s assume the user selected opt1 and opt2 (1st and 2nd quarter), txtBegin would be 1/1/CurrentYear and txtEnd would be 5/31/CurrentYear?

If the user selected opt2, opt3 and opt4 (2nd, 3rd and 4th quarter) then the txtBegin would be 4/1/CurrentYear and txtEnd would be 12/31/CurrentYear.

BEAUTIFUL…..Oh thank you Hans.

What if I wanted to include another checkbox (opt5) and title it “QTD” to pull all current quarters for the year? Would I change For i = 1 To 4 to For i = 1 To 5 and change this strIn = “Qtr In (” & Mid(strIn, 3) & “)” to strIn = “Qtr In (” & Mid(strIn, 4) & “)”?

Good Afternoon,

I have 4 option buttons, opt1, opt2, opt3, and opt4. Each option button represents the Qtr. The user may want to view a report based on a specific QTR so by selecting the qtr (option button) they want then the value needs to be passed to a query.

I’m not sure on how to build the logic to capture which option (and they can select more than 1) the user selected and I don’t know how to pass that value to the query.

I have the query built, I have the report built, I just need to pass the user’s request to the query.

For instance.

if user selects option 2 and 3, I need to pass “2 and 3” as a criteria to the query to only pull the data for 2nd and 3rd quarter.
if user selects option 4, I need to pass “4” as a criteria to the query to only pull the data for 4th quarter.

Can someone please point me in the right direction.

Yes it does…thank you very much.

I resolved the problem. See code.

[indent]

---

Long piece of code moved to attachment by HansV

---

[/indent]

I moved the table to another db on the server and everything went fine. Thanks Hans for your assistance, as always, it’s appreciated.

Hans,

I changed the code as suggested to:

[indent]

---

connectionString = “ODBC;DRIVER={SQL Server}” _
+ “;SERVER=” & “KMHPSQLKY20” _
+ “;DATABASE=” + SrcDatabaseName + “;”

---

[/indent]

but get the same error. Oddly, I can put the table in a query, see all the fields but when I go to run the query, I get the same message.

I think I have it.

[indent]

---

Between DateSerial(Year(Date())-1,Month(Date())-2,1) And DateSerial(Year(Date())-1,Month(Date())-1,0)

---

[/indent]

If anyone knows of a more effecient way, please let me know.

Thanks Hans, that’s what was missing. I knew to put the pound sign in, but was putting the last one in in the wrong place.

Hans,

I believe I’ve resolved it. See below:

[indent]

---

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim mySQL As String

mySQL = “SELECT tEmail.EmailAddy ” & _
“INTO tblTemp ” & _
“FROM tEmail ” & _
“WHERE Send=True;”

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

Set db = CurrentDb()
Set tdf = db.TableDefs(“tblTemp”)
Set rst = db.OpenRecordset(“tblTemp”, dbOpenTable)

---

[/indent]

Thanks for the assistance.

I’ve tried that several times earlier but thinking I missed something I tried your suggestion again and still I get the following error msg.

[indent]

---

Run-Time error 2342

a RunSQL action requires an argument consisting on an SQL statement

---

[/indent]

I got it Hans, as always, thanks so much for your help

Ok…I’ve modified you suggestion as you said to and it provides me with the count but the count appears like it was a totals query rather than a select query. i.e.

123456789 5
987654321 3

I need each record to appear with the count next to it.

123456789 1
123456789 2
123456789 3
123456789 4
123456789 5

987654321 1
987654321 2
987654321 3