Just wanted to Thank you for this. I know its been several years since posting but this was the most comprehensive and detailed guide I've found and it was extremely helpful!
Thank you so much for this tutorial! I just want to add a little tidbit, in case anyone else has the same error I did due to the field being set in Date/Time Format. In the code, of the search button add the DateValue command. So instead it'll read: strCriteria = "(DateValue([Order Date] >= #" & Me.OrderDateFrom & "# And DateValue([Order date])
Hi there, thank you so much for the tutorial! I worked for me, however, for some reason the date search only works if it is typed in as the American Format (i.e. MM/DD/YYYY), even though the data in my table is DD/MM/YYYY. Would you have any idea on why this is? THANK YOU!
austin, hi, good tutorial, thank-you...i have a table with date in date/time format, however i find running your script does not return correct records when searched on 1/7/2016 - 9/7/2016, but it is correct when searched on dates higher than single digit days e.g. 10/7/2016 - 16/7/2016. No records are returned for any single day searches. If i search 1/7/2016 - 15/7/2016 it returned correct records. Is there something I could be doing wrong or does the table date format have to be text formatted? Hope you can assist with suggestions for frustrated learning programmer :-)
Tewan, hi. thanks for your reply. On your answer basis, i dug around and found this worked for me. Because I am in Australia, we use dd/mmm/yyyy as the format. So my strCriteria was.... strCriteria = "([ActivityDate] >= #" & Format(Me.srchstrtdate, "dd/mmm/yyyy") & "# And [ActivityDate]
This was a big help. Calling myself a VB novice would be an understatement. If I had an addtional range of values to search, so that there were 2 serperate and independant ranges of criteria to look at, how would I add it? Example for above: Date and Total. A date range and a range of total value/cost? Thank you in advance.
HI Sir Great Video Please May you advise how to total a field after the search i.e. if i search between a date range to have a box indicating the sum of the field totals
hello I tried your code, but it applies it only once, if I repeat it the first one remains stored even by pressing the cancel filter keys or show the records. what am I doing wrong?
Hi Nana, Thank You so much for your tutorial. it works great. Now i have another issue that my date search doesnt display any records with null date values for my report. can you please help me out with this???????????????????
Dear Austin I can use vba code same as in your video tutorial. But search button option is not working the other buttons like clear and all records options is work perfect. So, you please give suggestions for solve the problem.
in your video, you manually entered a date instead of through date picker, any reason? in my case, I have range error if through date picker and ok if manually entered but in reverse date and month. Please enlighten me on this. thanks very much...
HI, I've been able to use the info and got everything working except for the Count. No matter how much I try and tweak, it's just not happening. Any ideas?
can u please show me how to make a search form with all tables at once not selective. i mean combining all tables to one search from, if i have 5 tables so i should be able to search data in all 5 tables at once please help from scratch...
Thank you. I've learned a lot from your video. I have a question how about if i dont want to include the year on the date. Just only the month and day only. How can i do that? Pls help, I used it in our church data base at its working well. I applied it on how to know the person that will celebrate their bday and wedding anniv one week before sunday. Thank u for your favorable response
Search button is not filtering the date range specified. I tried not to use the square brackets around Order Date because mine is "InvDate", but it failed to filter. Please help
I cannot do that like you. When I press on search button I got error message Run time error 3000 (-3201). When I click on debug I see that in code "DoCmd.ApplyFilter task" is higllighted :/ Any ideas?
task = "select * from tblemp prod table where (" & strcriteria & ") order by [date]" DoCmd.ApplyFilter task whats wrong here i ran the code 20 times but still coming back with syntax error. plz any help would be appreciated.
Excellent tutorial. Thank you very much. Can you please guide if it is possible to enter just one date instead of date range and view the records for that one date only?
hello Mr. Tewan😊 im following your ms access programming as of now I am doing an office project that will benefit my department..I just followed this video I created a search form. My questions is How about the combination of the DATE (Start date to End date) and combo box all together? can you help me give an example specially the codes that is needed to create this form..thank you in advance😊 hoping that you could help me
I have the sample file my website. However, it needs a little donation for my hard work. Thank you. link here: www.iaccessworld.com/downloads/search-multiple-fields/
Hello Austin, great video. I learned a lot from it and I like the way you present it step by step. I'm having an issue when i try add the findrecordcount function in there. Run-time error '3078': The Microsoft Access database engine cannot find the input table or query "tbl_StudentInfo". Make sure it exists and that its name is spelled correctly.
HI Austin . Please, help me to display a calculated Field in a form to be displayed in a report. Notice that this calculated value shows number of Family members(in sub form) which means The Father(in main form ) + the rest of family (All in one form). Moreover the calculated value is not reflected in any table. Access 2010 Thanks in advance
Firstable amazing video but doesn't work :( Can you help please? I apply as you mention with the "Me" Declaration in the code but Excel vba don't recognize Dim StrCriteria, task As String Me.Refresh If IsNull(StartDate) Or IsNull(FinalDate) Then MsgBox "ESCRIBIR FECHA", vbInformation, "FECHA REQUERIDA" StartDate.SetFocus Else StrCriteria = " ([Fecha]>= #" & StartDate & "# And [Fecha]
Hi Austin, I have used your exact codes with just the name change but still I got an error message for the below code "Me.RecordSource = task" Run time error 2580 The record source "Select *From From ProjectedLabourCostbyCategory1 order by [DATE]" secified on this form or report does not exist. I have the below code written. Please advice how to correct. Private Sub Command18_Click() Dim task As String Me.DateFrom = Null Me.DateTo = "" task = "select * From ProjectedLabourCostbyCategory1 order by [DATE]" Me.RecordSource = task
Thanks @austin72406 , it helped me in my task. However, After searching data between two dates, I need to generate sum of a particular field in the data and also, generate a report of that data searched. Could you please help me with that ? Thanks again!
I use MS 2016 but when I clicked Property sheet>> Event>> On Click>>> Event Procedure>> 3 dots system give me error @ Access failed to create the Visual Basic module What happens please help mee((
Hi Austin, I have tried to modify your example in this video but I receive nothing as a result of the search. Please help. Private Sub CmdSearchDate_Click() Dim strCriteria, task As String If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then MsgBox "Tanggal keduanya harus diisi", vbInformation, "Date Range Required" Me.StartDate.SetFocus Else strCriteria = "([dBKK] >= #" & Me.StartDate & "# And [dBKK]
How can you create a date range for a subform from a form? I saw your video for how to create a combo box for a subform but I need to know how to use a date range. Can you help? Thank you in advance.
you will use the search function with date range criteria on main form then assign as a recordsource on subform. like: strSearch = "SELECT * FROM TableName where " & Criteria & "" Me.Subformname.RecordSource = strSearch If you are using a split form then you can use a filter like: Me.FilterOn = True DoCmd.ApplyFilter strSearch
i have try these code but when i have type 01/08/2016 and 10/08/2016 it also showing 08/01/2016 and 08/10/2016 please help me (:)))))):(((((((((:((((())))))))
Could you please help me with this... . I am getting a runtime error in which "DoCmd.ApplyFilter" is highlighted. How to rectify this error. I am using the query, so I don't know if I have to include different expression because the screen show me Syntax error (missing operator0 in query expression .
I just copy exactly as this video. On Click (Event Procedure) it will open the VBA window. Then right click on the left panel (Project Database). Then click Insert>Module. All you have to do now is copy exactly like he type in this vids : Function FindRecordCount(strSQL As String) As Long Dim db As Database Dim rstRecord As Recordset 'On Error GoTo ErrorHandler Set db = CurrentDb Set rstRecords = db.OpenRecordset(strSQL) If rstRecords.EOF Then FindRecordCount = 0 Else rstRecords.MoveLast FindRecordCount = rstRecords.RecordCount End If rstRecords.Close db.Close Set rstRecords = Nothing Set db = Nothing End Function Save it and everything should be ok.
There is actually another way which is more simple than this. Just use Count function. First create a label or text box on your form. Then open the Property Sheet. Under the Data tab, u will see Control Source on the 1st row. Just type =Count(*) This function will do the same job which is counting the number of the record in your recordset below. Good luck!
Same here. It works fine in the split form itself, but when I try to place it in navigation form, all the controls work but it's only showing the top part of the form, not the bottom datasheet view with the results of the filter
How do I get this to work in dd/mm/yyyy rather than mm/dd/yyyy. strCriteria = "([Date 1st Contact]>= #" & Me.txtContactfrom & " # and [Date 1st Contact]
Private Sub ShowAll_Click() Dim task As String Me.IssuedDateFrom=Null Me.IssuedDateTo= "" task = "Select * from SearchbyDateRange order by [Issue Date]" Me.RecordSource = task
hi. thank you for the tutorial that you are uploading... i like this video.... how ever how can I search the upcoming birthday for the week regardless of the year using this date range ? pls help me.... kindly upload how can i do that....
you can set a birthday to the date of current year then perform a search function for next week. I don't have details at this time. but it is the logic above.
hi thanks for the reply.. but the problem is, it does not show anything when the i search the birthday for next week.. because it search only the current year...
hi thanks for the reply.. but the problem is, it does not show anything when the i search the birthday for next week.. because it search only the current year...
Hi Thanks for your videos, they are really helpful. However am getting a runtime error 3075. and the debug screen looks like this, but i cant seem to figure it out. Please help. Thanks
+austin72406 😯 I hope you can create a tutorial for it, I'm your fan and subsciber, and I almost created a database because I watch your video tutorials, that help me a lot,
You can use same criteria from Search function on report. Or you can create a temp query or table for a result of search function then set that table as a datasource of report to print
please create a video tutorial, for fiscal year, when you search an item/product/materials or etc., the total amount for fiscal year(April 2015-March 2016 = year 2015), (April 2016-March 2017 = year 2016) per item/product/materials will show, thank you so much! please....
