SQL commands in control source

fklein23
fklein23 used Ask the Experts™
on
I have looked at a few articles but I cant seem to find one that helps me. I have 6 text boxes that I need to show following queries:
SELECT Count(Transaction.ID) AS CountOfID
FROM [Transaction]
WHERE (((Transaction.Timestamp)=[Text4]) AND ((Transaction.Type)="Merit"))

Open in new window


SELECT Count(Transaction.ID) AS CountOfID
FROM [Transaction]
WHERE (((Transaction.Timestamp)=[Text4]) AND ((Transaction.Type)="Demerit"))

Open in new window


SELECT Count(Detention.ID) AS CountOfID
FROM Detention
WHERE (((Detention.Time)>0) AND ((Detention.Timestamp)=[Text4]))

Open in new window


SELECT Count(Transaction.ID) AS CountOfID
FROM MISC INNER JOIN [Transaction] ON MISC.[MiscPt ID] = Transaction.Descript
WHERE (((Transaction.Timestamp)=[Text4]) AND ((Transaction.Type)="MISC") AND ((Transaction.Descript)=5))

Open in new window


SELECT Count(Transaction.ID) AS CountOfID
FROM MISC INNER JOIN [Transaction] ON MISC.[MiscPt ID] = Transaction.Descript
WHERE (((Transaction.Timestamp)=[Text4]) AND ((Transaction.Type)="MISC") AND ((Transaction.Descript)=6))

Open in new window


SELECT Count(Transaction.ID) AS CountOfID
FROM MISC INNER JOIN [Transaction] ON MISC.[MiscPt ID] = Transaction.Descript
WHERE (((Transaction.Timestamp)=[Text4]) AND ((Transaction.Type)="MISC") AND ((Transaction.Descript)=2))

Open in new window


Text4 is a box that is in the header. I want to be able to enter a date into that box and have the fields fill in.

I tried this:
Me.[SnapshotReport].RowSource = "SELECT Count(Transaction.ID) AS CountOfID
FROM [Transaction]
WHERE (((Transaction.Timestamp)=[Text4]) AND ((Transaction.Type)="Merit"))"

Open in new window


But it said it was invalid. I have ran all the queries with a value input where [text4] is and it ran fine (in SQL mode at least).

What am I missing?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
you can't use the sql statement in the control source of the textbox, you have to use dcount()

place the codes in the afterupdate of Text4

me.text1=dcount("*","Transaction","Timestamp=#" & Text4 & "# and [Type]="Merit")

Top Expert 2016

Commented:
correction

place the codes in the afterupdate of Text4

me.text1=dcount("*","Transaction","Timestamp=#" & Text4 & "# and [Type]='Merit'")

Author

Commented:
So my code now looks like this:

Private Sub Text4_AfterUpdate()
Me.Text0 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='Merit'")
Me.Text2 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='Demerit'")
End Sub

Open in new window


But when I put a date in and hit enter it does nothing. I cleared out the boxes and they are all unbound.

Also how would one write the 3rd and 4th code?
Top Expert 2016

Commented:
better upload a copy of the db... upload a .mdb version

Author

Commented:
I'm pretty sure I included everything needed for those queries.

Let me know if there is something missing
Test.mdb
Top Expert 2016

Commented:
before i proceed, i saw that you are working in a report to display the values.

is that correct a report and not a form?
Top Expert 2016

Commented:
and, there is no afterUpdate event for a textbox in a report.
the afterupdate event of a textbox is available if the textbox is in a form.

Author

Commented:
yes this is a report

is that the reason it wouldn't work? If so what am I suppose to do?
Top Expert 2016

Commented:
it will help if you will discuss the main objective of this project..

Author

Commented:
Tags: Access, Reports, Queries

Didn't think I had to...

Author

Commented:
its just a simple report... show the values based on a date range... print

Author

Commented:
I tried this using the queries I build and dropped them into the form but you have to enter the date 6 times

Author

Commented:
Edit: not form... report sorry
Top Expert 2016

Commented:
you will need a form to enter the date parameter
the values that your report will display will be based on the entered date in the form...

is this ok?

Author

Commented:
Let me try this and see how it works. I'm still not quite sure how to write the rest of the queries though. I know SQL but only the basics. VB is still a work in progress for me.

Author

Commented:
I tried and It appears that the first queries did work and If you could just provide the syntax or a tutorial I can probably take it from here (unless you wouldn't mind showing me one of them).

Thanks

Author

Commented:
Me.Text6 = DCount("*", "Detention", "Timestamp=#" & Text4 & "# and [Time]='>0'")

Open in new window


Tried that for the 3rd query and got a data mismatch... Time is formatted as a number field so I'm not sure what I did wrong there
Top Expert 2016

Commented:
try this

Me.Text6 = DCount("*", "Detention", "Timestamp=#" & Text4 & "# and [Time] >0")

Author

Commented:
That also worked... I tried this:
Me.Text8 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='MISC'" & "# and [Descript] 5")
Me.Text10 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='MISC'" & "# and [Descript] 6")
Me.Text12 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='MISC'" & "# and [Descript] 2")

Open in new window


For the last 3 but I get an error... I also tried this

Me.Text8 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='MISC'" & "# and [Descript] =5")
Me.Text10 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='MISC'" & "# and [Descript] =6")
Me.Text12 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='MISC'" & "# and [Descript] =2")

Open in new window

Top Expert 2016

Commented:


Me.Text8 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='MISC' and [Descript] =5")
Me.Text10 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='MISC' and [Descript] =6")
Me.Text12 = DCount("*", "Transaction", "Timestamp=#" & Text4 & "# and [Type]='MISC' and [Descript] =2")

Author

Commented:
No error message but also the count is wrong... when I use the date 8/2/2011 (which should have values for every single one of the items, it just displays a 0. I'm not sure what the issue is. I have uploaded a copy of the form and the tables.
test.accdb
Top Expert 2016
Commented:
your field Timestamp, have time values in it.

see the changes made in the code

         change  "Timestamp" with  DateValue([Timestamp])
Test.accdb

Author

Commented:
That did it. Thanks Cap.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial