I have a form that has two (2) calendar dates 1. fromdatetext, 2. todatetext. I would like to be able to search the records from query "Emp_Lst_Frm_Query" and give the sum results from the dates. I would like to use the "Sub_Cmd" button to activate the search. I will be commenting out the case statement so the button only does the search for the dates.
I would like to be able to put total records in TotalTxt and then populate Incident, Request, and ChangeOrder fields with the proper sums.
The calendar controls work perfect.
Here is the code for the entire form, I am currently doing things one piece at a time. Then when things are working the way I like, I then combine them. - Option Compare Database
-
-
Dim Originatorcmb As ComboBox
-
Private Sub ocxcalendar_Click()
-
Originatorcmb.Value = ocxcalendar.Value
-
Originatorcmb.SetFocus
-
ocxcalendar.Visible = False
-
Set Originatorcmb = Nothing
-
End Sub
-
Private Sub fromdatecmb_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
-
Set Originatorcmb = fromdatecmb
-
-
ocxcalendar.Visible = True
-
ocxcalendar.SetFocus
-
If Not IsNull(Originatorcmb) Then
-
ocxcalendar.Value = Originatorcmb.Value
-
Else
-
ocxcalendar.Value = Date
-
End If
-
End Sub
-
Private Sub todatecmb_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
Set Originatorcmb = todatecmb
-
-
ocxcalendar.Visible = True
-
ocxcalendar.SetFocus
-
If Not IsNull(Originatorcmb) Then
-
ocxcalendar.Value = Originatorcmb.Value
-
Else
-
ocxcalendar.Value = Date
-
End If
-
End Sub
-
Private Sub Sub_Cmd_Click()
-
-
Dim typeCount As Integer
-
-
Select Case typeCount = DCount("[workedby]", "Emp_Lst_Frm_Query", "[workedby]= '" & Me.empcmb.Value & "'")
-
Case Me.empcmb.Value = "1"
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[workedby] = 'MJA'")
-
Me.TotalTxt.Value = typeCount
-
Me.Repaint
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Incident' And [workedby] = 'MJA'")
-
Me.Inc_Text.Value = typeCount
-
Me.Repaint
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Request' and [workedby] = 'MJA'")
-
Me.Req_Text.Value = typeCount
-
Me.Repaint
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Change Order' and [workedby]= 'MJA'")
-
Me.CO_Text.Value = typeCount
-
Me.Repaint
-
Case Me.empcmb.Value = "2"
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[workedby] = 'MOB'")
-
Me.TotalTxt.Value = typeCount
-
Me.Repaint
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Incident' And [workedby] = 'MOB'")
-
Me.Inc_Text.Value = typeCount
-
Me.Repaint
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Request' and [workedby] = 'MOB'")
-
Me.Req_Text.Value = typeCount
-
Me.Repaint
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Change Order' and [workedby]= 'MOB'")
-
Me.CO_Text.Value = typeCount
-
Me.Repaint
-
Case Else
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[workedby] = 'SLT'")
-
Me.TotalTxt.Value = typeCount
-
Me.Repaint
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Incident' And [workedby] = 'SLT'")
-
Me.Inc_Text.Value = typeCount
-
Me.Repaint
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Request' and [workedby] = 'SLT'")
-
Me.Req_Text.Value = typeCount
-
Me.Repaint
-
typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Change Order' and [workedby]= 'SLT'")
-
Me.CO_Text.Value = typeCount
-
Me.Repaint
-
End Select
-
End Sub
I would like to thank everyone in advance because this site has been a great resource.
8 2710 NeoPa 32,556
Expert Mod 16PB
Check out Example Filtering on a Form then, if you still have problems with it, post an explanation of where you're struggling and we can help you.
Thanks NeoPa, that was some good information, however, I am trying to filter based on a date range input by a "from" and a "to" date, an "employee name"(only three), and the "work order type". I can post the entire code here if you like, and I can try to post a picture of the form.
In my code(I am a very "NEW" person to this) I am trying to do a few different things. I will make those posts when I get to that point in the process. Currently I am trying to filter the data according to the requirements in the above paragraph.
Would you like to see the entire code? Also, again I am new to this posting stuff, what would i use for a URL if the image to the form is on my desktop?
NeoPa 32,556
Expert Mod 16PB Michael Adams: Thanks NeoPa, that was some good information, however, I am trying to filter based on a date range input by a "from" and a "to" date, an "employee name"(only three), and the "work order type". I can post the entire code here if you like, and I can try to post a picture of the form.
That sounds very much like what the article helps with Michael. The format for date ranges (any ranges actually) is to use the Between construct. Without going into exhaustive detail (as most of the rest of it is covered in the article) date criteria in SQL would look something like : - WHERE ((...)
-
AND ([DateField] Between #1/1/2010#
-
And #12/31/2010#)
-
AND (...))
As for seeing your code, I may ask for that at some point in the process, but for now we should be concentrating on the code that it should be like.
Pictures are rarely worth the effort. I have an understanding of what I need to know. If I didn't then I'd expect you to explain more clearly rather than drop a piccy in. There's only certain things where they can actually help in my view. Michael Adams: In my code(I am a very "NEW" person to this) I am trying to do a few different things. I will make those posts when I get to that point in the process. Currently I am trying to filter the data according to the requirements in the above paragraph.
Sounds a good approach. Too many questions (>1) in a single thread can soon get everything very complicated. Feel free to post a link in here to any new threads you post that are related. Michael Adams: Would you like to see the entire code? Also, again I am new to this posting stuff, what would i use for a URL if the image to the form is on my desktop?
No need for your code at this time.
There is almost no way (and certainly no safe way) to publish anything from your desktop to the web. This statement isn't absolute, but I think it's true enough from your perspective. Whenever you feel the need to share such a picture then use a third-party web to host it and post a link.
I am now getting a data type mismatch in criteria expression in this line code. - Select Case Me.cmbEmp.Value
-
Case "Michael J Adams"
-
mycount = DCount("[type]", "Emp_Lst_Frm_query", "[employee] = 'Michael J Adams' And [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")
-
Debug.Print [CUDate], [Employee], [Type], mycount,[fromdatecmb], [todatecmb];
-
Me.TotalTxt.Value = mycount
-
Me.Repaint
In the debug I am getting these data returns:
[CUDate] = 5/20/2010
[Employee] = "Michael J Adams"
[Type] = "Change Order"
mycount = 0
[fromdatecmb] = 12:00:00 AM
[todatecmb] = 12:00:00 AM
I believe that is where my mismatch is. the [fromdatecmb] and the [todatecmb] in the form is not pulling in the date
I figured it out! YEAH.
Here is the final code I came up with: - Select Case Me.cmbEmp.Value
-
Case "Michael J Adams"
-
mycount = DCount("[type]", "Emp_Lst_Frm_query", "[employee] = 'Michael J Adams' And [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")
-
Me.TotalTxt.Value = mycount
-
Me.Repaint
-
mycount = DCount("[Type]", "Emp_Lst_Frm_query", "[Type] = 'incident' and [employee] = 'Michael J Adams' and [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")
-
Me.Inc_Text.Value = mycount
-
Me.Repaint
-
mycount = DCount("[Type]", "Emp_Lst_Frm_query", "[Type] = 'Request' and [employee] = 'Michael J Adams' and [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")
-
Me.Req_Text.Value = mycount
-
Me.Repaint
-
mycount = DCount("[Type]", "Emp_Lst_Frm_query", "[Type] = 'Change Order' and [employee] = 'Michael J Adams' and [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")
-
Me.CO_Text.Value = mycount
-
Me.Repaint
NeoPa 32,556
Expert Mod 16PB
Good post! We are not going to be long sorting this out if you respond as clearly and intelligently every time.
I can see in your line #3 that you are formatting your dates wrongly (See Literal DateTimes and Their Delimiters (#)). The delimiters should be hashes (#) and the date strings themselves should be formatted as m/d/yyyy.
It also appears that no dates have been selected at run-time.
This code should work, but you'd need to ensure valid dates were entered in order to test it properly : - mycount = DCount("[type]", _
-
"Emp_Lst_Frm_query", _
-
"([employee]='Michael J Adams') And " & _
-
"([cudate] Between " & Format(Me.fromdatecmb,"\#m/d/yyyy\#") & _
-
" And " & Format(Me.todatecmb,"\#m/d/yyyy\#"))
PS. I see you've been busy while I've been working on this. Good for you.
For the Date data I am using a calendar control which is activated by a mousedown event in each datecmb box. Will I still need to format the date in the way you suggested?
NeoPa 32,556
Expert Mod 16PB
Absolutely. It could have all sorts of ways of displaying otherwise. In your earlier post they seemed to display as times only, which would presumably format your SQL string in the same way. Not much use to you.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Lucky |
last post by:
I have a vb.net windows app that connects to an access database. The
database has 1 table. One of the columns is called "Address". A user
enters any address into a textbox (txtValue) then clicks...
|
by: Access Newbie |
last post by:
I'm using Access 2000 and I'm trying to create a pass-through query to
append all the data from a local table to a remote table.
I'm using the SQL query editor in MS Access to create the query (I...
|
by: SC |
last post by:
I'm developing a site that may eventually have a very large database of
users (hopefully, but who knows). It will be a community website where
users can search each other (think Friendster,...
|
by: turbosatan |
last post by:
i need to send a query from a web app to a search page on my site
i understand that to use googles search i would use something along the lines of
http://www.google.com/search?=q
but the...
|
by: Frankie |
last post by:
I'm trying to run the following search query:
$query =
sprintf ("SELECT itemNumber, thumbnailURL, title, description, price
FROM apparel,hats
WHERE apparel.title OR apparel.description OR...
|
by: vHTML |
last post by:
hello everyone :)
I am trying to make a page which would take a "entered" on search query from the previous page and display results in google with that query in an <iframe> in the results.htm...
|
by: fusonts |
last post by:
Can someon show me how to wirte a mySQL search query to search from only the open records. This is what I have but it continues to return records that are closed so I'm obviously doing something...
|
by: manoj9849967222 |
last post by:
Hi All
I have a great problem. Is there a way to filter records using dates.
I have a table called sales. now i want to filter record say from 1/1/2006 to 1/1/2007. which would show me the sales...
|
by: Grubsy4u |
last post by:
Grubsy4u
Newbie
7 Posts October 5th, 2007
11:31 AM
#1
Report calculations
--------------------------------------------------------------------------------
|
by: paankhate |
last post by:
Hi,
I have a task at hand to reduce the time taken for search query to
execute. The query fetches records which will have to sorted by
degrees away from the logged in user. I have a function...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |