How do I make a single table database searchable with form input and output?

[Scott Deming]

Ok here's the deal. I'm in Afghanistan and attempting to create an Access database for my pilot's to debrief on when they come back from a mission. Included fields would be things like names, mission, time, etc. etc. We currently have an Access database that has been grandfathered down from unit to unit that kind of works. The issue with this database is that it is incredibly messy, unorganized, and half of the original functions of the database don't work anymore. So last week I started making a new database from scratch.

Prior to last week I'd never touched Access in my life. Therein lies my problem.

I've spent countless hours building this new database, learning the ins and outs of Access, and scouring forums trying to find the answers to my problems. As of right now the basic function of the database works. Data can be input to the form I created and is stored on the database back end. That's about the extent of what it can do.

What I need to accomplish is having a form that I can use to search specific fields of the database for date ranges, who was flying that mission, what the mission was etc. and then return those results in a chopped down version of the database that opens on the form. (this works in the current version, and I have the general idea of how to do it, but it's not working) The chopped database that opens needs to have a link that displays the original form with all the appropriate data in the appropriate fields. Like I said, the old database does exaclty this and it works fine... mine doesn't.

More importantly I need to be able to export the data to other programs. Certain data will need to make graphs, other parts of data will create a chopped down version of the database in an Excel doc with the information needed. I don't even know where to begin here.

Ok, here is how my database is set up:
- 1 Table for storing data (tblDebrief)
- 1 Form for inputing data into that table (frmDebrief)
- 10 Tables that correspond to combo boxes on the form for inputing the data (Source:AO, Source:Mission, Source:Team, etc.)
- 4 forms for getting the data to provide the answers I need (frmSearch, frmExport, frmView)
- 1 Switchboard with links to all the Forms (this actually works almost perfectly, and all the forms link back to the switchboard, yay! The only issue with this is that when I set the switchboard to "popup" it is physically impossible for me to go back into the design view of the switchboard)

I realize that's alot to take in Believe me I'll be at this computer waiting for responses and any help at all until I get this damn thing fixed.

The main thing I'm asking for are responses that are in basic English. Yes, I can understand terms like Forms, Tables, Queries, and I know a little bit about VB programming from back in the day, but you start talking in all out technical language and I'm lost. If there are posts anyone knows of that can help with what I'm looking for please post it. I am fully prepared and willing to dig through anything that can be of use. I'm not asking anyone to make this database for me, just please point me in the right direction because right now I am LOST!

[munkee]

Since this seems to be for an epic good cause I dont mind spending quite a bit of time taking a look at this with you.

For starters the search functionality. The best way I have seen for looking at inputting date ranges and multiple criteria is by building a dynamic "where" string. This is essentially using some VBA code to filter your database down "where" certain criteria have been selected.

The best example of this was created by Allen Browne and I will link you outright to his site first and then directly to the code I am talking about.

His site will explain quite a few tricks you will be able to utilise in your database with clear explenations, I was in a similar boat to yourself just 8 months ago and now I feel I know a decent enough amount of code/techniques to get most databases going in the right direction and I used Allen's site religiously:

http://allenbrowne.com/tips.html

Now to the creation of the search:

http://allenbrowne.com/ser-62.html

If you get stuck with any of the search stuff let me know and I will provide a cut down example of his code to get your head around it a bit more (but it is often worth trying to plug away at this yourself.)

The second thing you mentioned was pretty much "being able to view full information of returned search results". In your current database it is likely you perform a search on the main table and you are returned a number of records giving a brief "overview" of all the data that is there. Usually there will be a button beside each of the returned records which you can click to open up a form with the "full" record/information behind it.

If this is what you are talking about, I recently answered a topic where a guy asked something very similar and I posted an example database which you might be able to view just to clarify it is the functionality you are after. The thread is here:

http://bytes.com/topic/access/answer...m-summary-form

If there is anything else you need to know just post again and to be honest I am sure you will have more specific questions next ;)


Edit:

Sorry for ignoring the exporting results to other programs such as excel etc, I do have some code that will do this but until you have the search form returning results as you want correctly then it might be worth holding fire with the exporting for now.

[Scott Deming]

Munkee,

Thank you so much for the answer. I'm digging around through those sites now and they are definitely helping. For starters, I realized I didn't have any of my other forms linking to the main table, they were all just floating around with nothing to reference to.

I'll post an update tomorrow after I've had a chance to implement all the changes and see where I'm at then.

Thanks again, I really appreciate it!

[munkee]

No problem.

The method Allen uses is to place search options in the forms header.

I use a slightly different setup where I create a subform with recordsource set to query of a table I want to search on. With the form style as "continuous". I then place text boxes on this subform to house the fields of the underlying table in a more "overview" style, so not all fields are represented with a textbox.

I then add a button beside this setup with the text "view" on it. This will then open up a form when clicked which houses the whole of the record with full information.

My search form contains this subform and I place the search boxes/options etc on this main form and a simple search button.

To illustrate all of this a bit more clearer I have attached a file with some screen shots and basic explenations of why/how.


Continous Subform based on either a table or a query.zip

[Scott Deming]

ok, i'm beating my head against a wall again. sorry it took so long to respond. it got pretty busy there for awhile.

anyways, i've put in all the code. edited to be applicable in my database, checked checked checked and double checked all the spelling on everything. it's only like 2 or 3 pages so it doesn't take too long to check. and i still have nothing.

when i click on the box, there's no error, or click to debug, there's nothing. it's still acting like an empty command button. i tried switching it from the header to the detail section, but that didn't help either.

i hate to be a pest but can you barney style this for me? i don't get what i'm doing wrong.

[dsatino]

Scott, there are numerous users of this site who could get this running fairly shortly if they had it. Probably fairly quickly as well.

You may want to consider making a copy, deleting the records from the copy, inputting some dummy records in the tables, and posting the copy in your post.

I've seen plenty of users here simpy write the code and post it back. Some of them do this as a hobby and are happy to it.

Even if no one undertakes the project as a whole, we'll have all the technical details on hand without you having to explain it and you can post single problems to solve which will lead to incremental, but specific fixes.

Just a thought.

[Scott Deming]

yeah you're right, i just copied all the code and put a bunch of images into a powerpoint. working on explaining what does what. i hadn't thought of dummy records though, thats a really good idea. i should have the file posted on here shortly. thanks.

[munkee]

Hmm probably needs compiling to find the errors and if not there might be a settings issue in your vba editor.. or code might not be pointing in the right directions. Best to upload..im around tonight to take a look at this if others aren't

[Scott Deming]

ok so here it is. i had to take all the classified bits out so it's an empty shell with only three entries that consist of "asdflk" in alot of the boxes.

I'm not really looking for someone to just fix it and go 'here you go'. i'd really like to know what i did wrong so i don't have this issue any more in the future. again, i really appreciate the help. i know this is asking alot and there's not alot i can do to thank you guys properly other than say thank you like a thousand times.

also, i made the powerpoint before i decided to attach the program. so it's kinda useless. but its there. :P

Access Problems.zip

[munkee]

Balls, Im using access 2003 so I cant open your file.

However looking at your setup I can see an issue. It seems to me that you are referencing your main form at the moment in all of your Me. statements. Your search and filter buttons are on your main form but you search needs to filter your subform. So to fix this problem:

Change:

Private Sub form_open(cancel As Integer)
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub
 

To point to the subform which will be something like me.subfrmblah.form.filter = "(False)" etc.

Also your:

        strWhere = Left$(strWhere, lnglen)
        Debug.Print strWhere
        Me.Filter = strWhere
        Me.FilterOn = True
 

Need to change the me. statements to point to your subform.#

Here is a site which explains the referencing:

http://blogs.techrepublic.com.com/msoffice/?p=188

I think it should be something like Me.subformname.form.filteron = true etc
or maybe me.subformname.filteron = true. Sorry not got a database to test this on at the minute and I always forget the correct convention. If you use me. and intellisense should come up in your vba editor and find the subform control name (the name of the subform box on your page) and then add in the .filter parts it should all come up for you.

All you need to take away from all this ramble is:

Because unlike in Allens original script you are not filtering the main form, you are filtering a subform all of your Me. statements are pointing to the main form and not the subform. So you are trying to filter a form which has no records on it, when you should be filtering the subform which does have records on it.

Your controls to create strWhere are all held on the main form which is fine, but the actual filter strWhere needs to be applied to the subform.

[Scott Deming]

    If Not IsNull(Me.frmsubSearch.Form.District) Then
        strWhere = strWhere & "([district] = """ & Me.frmsubSearch.Form.District & """) AND "
    End If

ok i fixed the specific codes you linked in the top part, but when it comes to each individual control is this what you are talking about? also, i noticed on the page you linked he uses "Me!" instead of "Me.", is that an issue or irrelevant?

[munkee]

For each individual control in this you need to do it based on what the code is saying.

So in your example above:

 If Not IsNull(Me.frmsubSearch.Form.District) Then 
 

This is a if statement to check whether the control on your main form contains anything. If it does contain something then we add on to our strwhere some extra filter information.

If it does not contain anything then "end if" (so dont do anything) and go on to the next statement.

So with that being said your line is referencing the subform when it should be referencing your main form (just Me.district).

The next line in your code is:

strWhere = strWhere & "([district] = """ & Me.frmsubSearch.Form.District & """) AND "

This is basically saying take the strwhere and add on to the strwhere this extra info which is to look at the field [district] in a recordset and look for the specific value supplied from your main form.

In this case you have referenced your subform, which you shouldn't as the main form is what supplies the value you want to go in to the filter. So revert your line:

Me.frmsubSearch.Form.District

To me.district or wherever your textbox value/combo box value is held in your main form.

The basic idea of the Allen Browne filter and the usage with the sub form is to basically do the following:

Use controls/textboxes/comboboxes in your main form to supply values which will go in to your strwhere. So you reference these using me.textbox.value or me.combo etc

Strwhere is built through multiple IF statements to see whether there has been anything supplied to be filtered on. Each time an IF statement criteria is met we have the code adding another bit of text to our strwhere string + an "AND" at the end. As we could have multiple criteria met and need an "AND" statement to join them all together.

This strWhere is then at the end of the day check to see if an "AND" statement is left on the end, if it is we just chop that last bit off and produce the final formatted filter strWhere, with all of the options we specified we want to filter/search by.

We then take strwhere and we basically say right now with all the options I selected I want to then apply this to my subform. My subform holds a recordsource which is equal to one of my tables. I want this table to be searched/filtered based on what I asked for.

So we take our strWhere, we reference the subforms filter property using me.subfrmname.form.filter and we set it equal to our filter string strwhere as:

me.subfrmname.form.filter = strwhere

We then turn the filter on using filteron propery and our table is then filtered down from showing every record to just showing the specific records that match our filter.


Here is some code from one of my projects:

'Date field to find end date for date occured
    If Not IsNull(Me.txtfilterDateEnd) Then   'Less than the next day.
        strwhere = strwhere & "([DteOccur] < " & Format(Me.txtfilterDateEnd + 1, conJetDate) & ") AND "
    End If

 'Listbox to select department responsible passing text value in sql query and not numerical
    If Me.lstfilterDeptResp.ItemsSelected.count > 0 Then
        Dim itm2 As Variant
        Dim strList2 As String
        For Each itm2 In Me.lstfilterDeptResp.ItemsSelected
            If strList2 = "" Then
                strList2 = "([DeptResp] = " & "'" & Me.lstfilterDeptResp.Column(1, itm2) & "'" & ")"
            Else
                strList2 = strList2 & " OR " & "([DeptResp] = " & "'" & Me.lstfilterDeptResp.Column(1, itm2) & "'" & ")"
            End If
        Next
        strwhere = strwhere & "(" & strList2 & ") AND "
    End If

The bracketed statements such as [DeptResp] and [DteOccur] are the names of the fields in my table.

Everything else such as me.lstfilterDeptResp.Column(1,itm2) is just referencing a combobox control on my main form, hence the me.controlname and not delving in to me.subfrm.form.controlname etc.

Once you get this referencing bit right, take a look at the debug.print strwhere line in your vba editor immediate window. Everything will click when you see one of these strwhere lines actually completed.

Here is a debug print of my strwhere just to show the construction:

([DteOccur] >= #01/06/2011#) AND (([DeptRaisedBy] = 'Engineering - Service')) AND (([NCStatus] = 'Initial Notification') OR ([NCStatus] = 'Pending Acceptance') OR ([NCStatus] = 'Action Being Taken') OR ([NCStatus] = 'Awaiting Closure') OR ([NCStatus] = 'Closed'))
 

As you can see the AND statements just link everything together unless an OR statement is there when I have multiple selections in a listbox.


Edit:

The period . and what is refered to as the bang ! dont make a blind bit of difference in their usage. They are more used to try and make code look a bit cleaner and explain what you are referencing. You by all means can use just .'s or just !'s .. I always use .'s because its just easy for me.

If you did want to follow the true coding style if you referencing a control on a form which will then have a reference to a control on that form below it you would use ! and then .

For example:

me.txtbox1
Me!subfrmform.form.txtbox1

It honestly doesn't matter though and it makes no difference to the code, nor does it change how fast things execute.

[mshmyob]

Just subscribing - will look at attached database and give my 2 cents.

cheers,

[Scott Deming]

ok so now that i've fixed that, if i'm understanding what you wrote my code is good to go. the problem is now with the subform table being referenced.

so when i open the search form, the entries to the form should already be displayed. then using the search feature would narrow down the displayed results to what i want to find or filtered for.

i linked the items in the subform directly from the debrief table and they use to function like that. where when i opened the form all of the entries were already there. but i changed something (not sure what, i'll figure it out though) earlier so now when it comes up the table is empty. therefore, when i apply the search function i'm sorting through an empty database. (edit: i checked and when the form is display independantly, it properly reflects the main form. but when the form is brought up as a subform, it is still displaying that there is nothing in it. like in the screen shots i put in the powerpoint, the only thing displayed is "new")

so essentially, once i fix the table and make it display everything intially THEN the search feature should work and display only what i want.

i hope i'm getting warmer on this. i'm not sure how but i can actually understand and comprehend what you wrote. there's hope yet! lmao

[munkee]

Try commenting out the filter=(false) and filteron = true in the form open event.

The false in the filter makes sure no records are shown and the filteron ensures that this false filter is applied. So might be worth removing it.

Then also check that your subform recordsource is definitely set to a table and that there is no criteria in the filter property in the form properties it may have a "(False)" sat in there from previously running code.

[Scott Deming]

k commented that out. now the table isn't blank when i open it, but it only displays 1 out of the 3 records i've entered into it. and the entries on the table don't change when i filter them.

the form filter property is blank so i'm assuming that means there's no code hidden in it, but i'm not sure how to check to make 100% sure.

it seems like everything is right minus like 1 or maybe 2 things.

is there supposed to be some other lines of code for general form/subform behaviors that i'm missing?

[munkee]

Can you save your database as access 2003 and I will take a look at it a bit more closely. I suspect it might be a setting in the form properties now more than anything.

Otherwise:
Can you put in some parameters in your input boxes and click search whilst you have the debug.print strwhere line uncommented in your code. Then just paste here your strwhere code that is printed in to the immediate window of the vba editor.

[Scott Deming]

yeah gimme a minute to burn it over with all the changes i made to it.

[Scott Deming]

ARGH! ok i can't do that. it says i'm using features that are incompatible with earlier versions of access. and the debug thingy has been uncommented but there's never any bug reports coming up.

i think i'm about to tap out for the night. x.x

[munkee]

Weird you should be able to press ctrl+g in vba editor and it will bring up the immediate window. This will then print out the debug.print part of your code.

[Scott Deming]

well it does pull up the immediate window, but it's blank??

[munkee]

Yea it will be blank, but then you should be able to close it and run your form and click your search button with some of your options selected so that it definitely creates a where string. Then go back to your vba editor and check the immediate window. It should place your strwhere in the immediate window.. if not then we have some weird issues going on..

[Scott Deming]

ok i did the search but nothing is coming up in the immediate window.

so does that mean that the button is missing some code, or is something else horribly wrong?

i do have macros enabled, and i tried it with the boxes both blank and with selections.

[munkee]

Right click on your submit/search button and check you have the name of the button set to cmdSearch

Another way to check this is to right click on it and "Build Event" then select coding and it should bring up your vba editor with all of your vba to do the search within the buttons on click event. If there is nothing in its on click event then you have the name of the button wrong.

[munkee]

Actually further to the point above, if the name of the button is correct and it still does not work then:

Right click your button and go to properties, go to the events tab and check to see whether the OnClick event has [Event Procedure] in the box beside it.

Access may have not picked up that you are copying and pasting code in to the vba editor and linked this back to the button.

[Scott Deming]

yay! ok yes it is called cmdSearch, but it was trying to run a macro in the "On Click" selection. I changed that to [Event Procedure] and now i'm getting the message box that there was an error and i have the option to debug.

Progress!

[munkee]

Nice one when you click debug which line is being highlighted?

[Scott Deming]

right now the if lines are highlighting which my first impression was that the box names were incorrect, but after like quadruple checking all the spelling their all correct. the one that was messed up i fixed and the strwhere=strwhere (code) line highlighted immediately below it. which leads me to again think its spelling but if it is i can't find the misspelling.

i'm still working through it to figure out all the bugs but i'm stuck on the if then statement regarding [AMC Last Name]. like i said, i've checked spelling and references numerous times but cant figure it out.

i'll post the actual code lines in a minute, i don't have access to that computer at the moment.

[munkee]

Yea just keep plugging away and something will click. If you can post up your whole code from the page I will try and recreate a mock up database here and go through it for you tomorrow. Otherwise time for bed for me I think and Ill leave a reply at some point before Sunday

[Scott Deming]

I've worked through alot of the debugging errors. Attached is a zip file with the program and back end, as well as two documents. The first has the immediate debug error when i hit the search button, and the second is my code for the search button.

Thoughts?


Debug errors.zip

[munkee]

I will tryand look over this tomorrow. Your immediate window is not showing an error it is showing strwhere because of the line debug.print strwhere. You can use this to check if strwhere is correct to be passed to the form filter. Anyway if it isn't filtering correctly or no records are returned comment out all the filter options bar the first and try again. See if the filter will work with just one selection. If it does uncomment the next and test again etc. one might be,set up wrong. If none work then its time to check the rest of the code. In my experience you might be passing a text value to a filter when infact you should be passing a number etc.

[munkee]

Your dates look very odd..

the deubg.print is showing:

and ([date] >= #06/m01/y116#) AND 

m01 and y116 will not be evaluated correctly I dont think.

Do you have some special way to make dates or something?

Also all of these:

 If Not IsNull(Me.frmsubSearch.Form.subDistrict) Then

Should really be saying Me.subDistrict etc.

You basically have text boxes and form controls on your main form which you reference using Me.controlname.

You then pass all of these control values to strwhere using the vba code we have.

Then strwhere is passed to the subform on your main form as a filter:

        Me.frmsubSearch.Form.Filter = strWhere
        Me.frmsubSearch.Form.FilterOn = True
 

So everything in the last part is correct. It is your reference to the main form controls which is not. These should, as I said be me.controlname.

For example if you have a textbox on your main from which is named txtDistricts you would do a search using this:

    If Not IsNull(Me.txtDistricts) Then
        strWhere = strWhere & "([district] = """ & Me.txtDistricts & """) AND "
    End If
 

Infact looking at your code you seem to have this correct in some places but not others ;)

    If Not IsNull(Me.frmsubSearch.Form.subTeam) Then
        strWhere = strWhere & "([Team] = """ & Me.Team & """) AND "
    End If
 

BOLD is correct and Italics is incorrect. The italics should be the same as the BOLD using the above code as an example =]

NEARLY THERE! and no doubt you will be learning a lot of vba mistakes by doing this. I know it is an annoying and slow process but if you had to do this all over again you would be 10000000x faster knowing what you do now.

[Scott Deming]

ok so we're making progress lol. i fixed all of the code where i had put "me.frmsubsearch.form.subcca" etc. and replaced with the correct "me.cca". I then commented out the date fields as i figure those will take a little bit more tinkering.

Now when i hit the search button, because i left everything blank i get the message box saying "nothing to do". Which from my understanding of the code is because all of the fields were left blank, but if everything is blank i want it to return all entries in the databse. (i don't think i said that earlier, my bad x.x)

so close!

[Scott Deming]

also, i don't know how to describe this so it may turn out a little wordy. but here goes.

i currently have 3 sample records in the database. when i do any type of search, only the first entry in that database will display. for example, each entry has a different AO, the first has Bandit, second has No Slack, and the last has Panther. If i select no slack or panther no records are displayed. If i select Bandit the one record is displayed. is this a setting or button i forgot to click?

[munkee]

Hmmm I will answer your first post now then have a think about the second and reply after work. when you load your form at start up does it show all records in the subform? I think I remember telling you to comment out the stuff in the onload event is that still done?

Im going to acquire a copy of access 2007 or 2010 tonight to help a bit further with your second issue. Actually make sure your subform is set to continuous form and not single in its properties section unless it is set to datasheet then it wont be this issue and we can try something else

[Scott Deming]

yeah it's still all commented out. but it only displays 1 entry when it opens instead of all 3. it does open in datasheet mode, i'm trying to find where to change it from single to contiuous.

edit: nevermind, it's in datasheet mode. i get what you were saying on that part now :P

[munkee]

Is the recordsource of your subform set to a table? Also check to make sure the subform has no filter set by checking the form properties page

[Scott Deming]

yes the recordsource to the subform is set to the main table that all info is put in to.

also, Filter on Load was set to no, but allow filters was set to yes. I changed allow filters to no but there was no change to the table displayed.

[munkee]

This is really odd if your subform is,set to a table it should load all the records. Try opening the sub form directly without loading the main form and see if they all show up. If they do we know it's something up with the main form if not it's the subform settings.

[Scott Deming]

yeah when i open the sub table all the records show, I finally figured out how to get the code from the original program and am working with that now. Through tinkering i just figured out the same thing you said 20 minutes ago lol. now to figure out wtf is going on.

[munkee]

If they all show in the subform then there is some code firing on the onload /onopen/events invoking a filter

Anyway guess you dont need to worry now you have the old code

[Scott Deming]

i'm not so sure about that. i still can't find anything thats causing only the most recent record to display. i've checked all the settings against the old form and they're all the same. i can't find any additional code invoking a filter on open, but i'm still looking.

but i'm not sure about the code being good yet, i've updated all the old with the new. it looks almost exactly the same as what you had given me, there were just a few things different. but until i figure out why it's only displaying the most recent i won't be able to see if the code is good or not.

[Scott Deming]

ok correction, it's not showing the most recent. it's showing only the first entry.

edit: holy crap i figured it out! I had the "Link master Fields" and "Link child Fields" boxes filled in with something, not even sure what it was. took that out and BAM! it works... almost.

It is now sortable by every field I have on the form EXCEPT for the dates. They are still completely irrelevant. If the date is the only thing I select then it dsiplays all the entries, if i leave the dates blank it still displays all the entries. Here's the code below:

If IsDate(Me.dateFrom) Then
    strWhere = strWhere & " AND " & "dateFrom >= " & GetDateFilter(Me.dateFrom)
ElseIf Nz(Me.dateFrom) <> "" Then
    strError = cInvalidDateError
End If
 
If IsDate(Me.dateTo) Then
    strWhere = strWhere & " AND " & "dateTo <= " & GetDateFilter(Me.dateTo)
ElseIf Nz(Me.dateTo) <> "" Then
    strError = cInvalidDateError
End If

[munkee]

Ultimately your getdatefilter() is a very very odd looking function.

What do you put in to your input boxes in order to supply a date to be filtered? (me.datefilter)

Is it a simple 01/12/2010 type of date? so dd/mm/yyyy or mm/dd/yyyy or a different format all together?

Edit:

This is where the debug.print strwhere becomes very useful as it will let you see how the date you put in to the boxes looks to the strwhere filter after all of your code.

[munkee]

Managed to grab your getdatefilter from some old code:

Function GetDateFilter(dtDate As Date) As String
GetDateFilter = "#" & Format(dtDate, "DD/MMM/YYYY") & "#"
End Function
 

Im not too hot on date formatting but I am sure your date filter is basically formatting a date which is inputted as 01/01/2011 in to a mash up of 01/Jan/2011.

It might be useful if you could paste a couple of records from your table which you are filtering just to see how this date looks as raw data.

I am tempted to say it also may be to do with your adjusted lines to do with const jet date.

Allen Brownes script shows:

Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

Which you have changed to:

    Const conjetdate = "\#dd/\mmm/\yyyy\#"

But you are also then using the function described earlier to reformat the date. As I said though better to look at the raw data to see where we go from here.

Edit:

Also if you could open up your main form and put a date in to one of the fields such as "Date from" and then switch to your vba editor (with the form still running so usually best to open vba editor in design view and keep it open whilst you switch to form view) and then go down to your immediate window and type:

?Forms!formname!controlname

(Where formname = name of the form open and controlname = name of the date textbox). Then hit enter it should spew out the value you have entered in to the date textbox in to the immediate window. If you can paste that in to here it will confirm the exact formatting for us.

[Scott Deming]

ok what popped up when i did that was: 1/19/2011

which isn't the format it's supposed to be in. the raw data would say 19-Jan-2011, as would the box you put the date in to search by.


edit: ok, i've changed formatting on all boxes for the date to Medium Date. Yet when i search it still changed the date to 1/19/2011. so i changed the format in the code back to DD/MMM/YYYY, but it still shows up as 1/19/2011.

also, in tinkering i've found that it does filter by the date to an extent. for example, if i search for 4 jan then no records show because all the entries are either 6 jan or 19 jan. but if i search for 6 jan, then all the entries show, including the 19 jan entry.

[munkee]

I will mess around with this at work in the morning it is definitely to do with date formats being wrong. Do you have input mask or format hdset on the textbox to make it show 19-jan-2011 or are you typing it in like that



F inallywith just the date code runnin can you paste your debug.print of strwhere. Comment out all the succesful textboxes we can just target the dates now Ooooo

[Scott Deming]

i put an input mask on it. i selected the default "Medium Date" for all the boxes associated with a date, including on the back end.

how do i do the debug.print again? i tried doing it in the immediate window and running the code but nothing came up.

[munkee]

Ok I set up my a database similar to yours and got the code working by remove the function parts and setting the constant jet date back to its original form:

Private Sub cmdSearch_Click()
 
    Dim strWhere As String
    Dim lnglen As Long
    'Const conjetdate = "\#dd/\mmm/\yyyy\#"
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
 
   If Not IsNull(Me.datefrom) Then
        strWhere = strWhere & "([date] >= " & Format(Me.datefrom, conJetDate) & ") AND "
    End If    
 
lnglen = Len(strWhere) - 5
 
    If lnglen <= 0 Then
        MsgBox "No Criteria", vbInformation, "Nothing to do."
    Else
        strWhere = Left$(strWhere, lnglen)
        Debug.Print strWhere
        Me.Table1_subform.Form.Filter = strWhere
        Me.Table1_subform.Form.FilterOn = True
    End If
 
End Sub

The bits below are what you need to change / check in your code. (Obviously check to make sure they make sense etc.)

    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
 
   If Not IsNull(Me.datefrom) Then
        strWhere = strWhere & "([date] >= " & Format(Me.datefrom, conJetDate) & ") AND "
    End If  

I would also recommend getting rid of the input masks I found they are quite confusing to be honest however I know this is likely being used because of international dates differing between countries so you want to explicity show what date you mean.

The reason why the

DD/MMM/YYYY, but it still shows up as 1/19/2011. 
 
 

Occurs I think is because regardless to however we enter dates access stores it as a value of dd/mm/yyy or similar. The masks we place or the formats we add to it just "dress up" this date to look how we want it to. But the true value always stays the same. This is probably similar to what we see in MS Excel. All dates are actually a serial code such as 403603 which I think is something to do with the number of days since some random base date MS selected. These codes are then "dressed up" as dates when we format the cells such as 01/02/2011 etc.