After Effect

yeah i know its not really used much now but i made a database using Access97 (in know its ancient but you have to see the computer im using =P) at work that has all our client details on it. When i was half way through making it i made a new form that was basically a search form, i can search for pretty much everything in the database and it works great.

Anyways back to what i was wanting...

i have a field in the database that has the box number that teh files are being stored in when they are dead filed.

thing is though i now need to add a criteria in the search form that will display the files in a range of boxes (i.e i need to display the files that in boxes 1-100) ive been wracking my brains on the code for it. Annoying thing about it is i actually already have a search criteria in the search for that will display the clients that files between 2 dates but i cant seem to figure out what the code is for between 2 numbers.

since its been about 7-8 years since i actually looked learned most of this stuff i was wondering if anyone else could help me.

The code i used for the date one is relatively simple its in bold near the bottom, i just cant seem to change it from date to number =|







Function fSubSQL()

Dim strSQL As String

strSQL = "SELECT * FROM tblClients "
strWhere = " ClientID Is Not Null "

If Not IsNull(Me("ClientSurname")) Then
strWhere = strWhere & " AND ClientSurname Like ""*" & Me("ClientSurname") & "*"""
End If

If Not IsNull(Me("ClientForename")) Then
strWhere = strWhere & " AND ClientForename Like ""*" & Me("ClientForename") & "*"""
End If

If Not IsNull(Me("ClientRef")) Then
strWhere = strWhere & " AND ClientRef Like ""*" & Me("ClientRef") & "*"""
End If

If Not IsNull(Me("OurFileNo")) Then
strWhere = strWhere & " AND OurFileNo Like ""*" & Me("OurFileNo") & "*"""
End If

If Not IsNull(Me("Matter")) Then
strWhere = strWhere & " AND Matter Like ""*" & Me("Matter") & "*"""
End If

If Not IsNull(Me("Address")) Then
strWhere = strWhere & " AND Address Like ""*" & Me("Address") & "*"""
End If

If Not IsNull(Me("FeeEarner")) Then
strWhere = strWhere & " AND FeeEarner Like ""*" & Me("FeeEarner") & "*"""
End If

If Not IsNull(Me("Status")) Then
strWhere = strWhere & " AND Status Like ""*" & Me("Status") & "*"""
End If

If Not IsNull(Me("Category")) Then
strWhere = strWhere & " AND Category Like ""*" & Me("Category") & "*"""
End If

If Not IsNull(Me("txtFrom")) Then
strWhere = strWhere & " AND OurFileDate >= #" & Format(Me("txtFrom"), "mm/dd/yy") & "#"
End If

If Not IsNull(Me("txtUntil")) Then
strWhere = strWhere & " AND OurFileDate <= #" & Format(Me("txtUntil"), "mm/dd/yy") & "#"
End If

If Not IsNull(Me("txtFromDF")) Then
strWhere = strWhere & " AND DeadFileDate >= #" & Format(Me("txtFromDF"), "mm/dd/yy") & "#"
End If

If Not IsNull(Me("txtUntilDF")) Then
strWhere = strWhere & " AND DeadFileDate <= #" & Format(Me("txtUntilDF"), "mm/dd/yy") & "#"
End If

If Not IsNull(Me("txtFromDest")) Then
strWhere = strWhere & " AND DestroyDate >= #" & Format(Me("txtFromDest"), "mm/dd/yy") & "#"
End If

If Not IsNull(Me("txtUntilDest")) Then
strWhere = strWhere & " AND DestroyDate <= #" & Format(Me("txtUntilDest"), "mm/dd/yy") & "#"
End If

fSubSQL = strSQL & " WHERE " & strWhere & " ORDER BY ourfiledate DESC"

Debug.Print strSQL

End Function

um im no expert since its been 4-5 years I've seen VB and im not 100% sure if i get your problem xD I'd probably do this only working with queries.

If it would be any help xD


ask for first variable
ask for second variable
just add "(Tabel.Id) Between variable1 And variable2" to your query

tabel.id is ofcourse how your table is called

dont forget to give those variables a standard value

yeah thats okay if everyone has access to well... access =P

most of the computers are just running an access runtime thing which will open a database but you cant directly edit the database its self so they cant run queries hence the reason i made a form that can do searches =P

if the database didnt have over 14k client details in it i would just post up a copy of it and let someone fiddle with it =P

basically what i want is this

i have 2000 different boxes (boxes in real life not in access =P)
and i use my search form to search for boxes 1-100 out of those 2000 and display the files that are in each of those boxes.

the code that i highlighted in bold should be very similar to what im wanting except that code there deals solely with dates and not numbers so it is not suitavle for what im wanting so it needs to be different, and after spending 6 hours on trying to look it up and figure it out ive given up and am asking you guys =P

edit: ill see what i can do about posting up a copy, i might just empty the client database and fill it with a load of rubbish so people can see what it does.

wat?

Never really looked into VB much, used it back in 2000 i think for some school project.

but would it not just be the same as that without the format bit?

If Not IsNull(Me("txtFrom")) Then
strWhere = strWhere & " AND OurFileBoxNum>= #" & Me("txtFrom") & "#"
End If

If Not IsNull(Me("txtUntil")) Then
strWhere = strWhere & " AND OurFileBoxNum <= #" & Me("txtUntil") & "#"
End If

something like that.

nah doesnt work, the #" refers to dates i figured it out though, the code i was using at work, worked no problem when i tried it at home, its prolly that shitty ass computer im using.

Code: Select all

If Not IsNull(Me("BoxFrom")) Then
strWhere = strWhere & " AND BoxNo >= """ & Me("BoxFrom") & """"
End If

If Not IsNull(Me("BoxTo")) Then
strWhere = strWhere & " AND BoxNo <= """ &Me("BoxTo") & """"
End If

VB is prolly one of the easiest programming languages ever invented, just temperamental computers that are the problem =\

well i figured it out in the end, the problem i was having was the fact i didnt change the boxNo field to a number field so it was just being a dick about it

also the code is slightly wrong its suppose to be this

Code: Select all

If Not IsNull(Me("BoxFrom")) Then
strWhere = strWhere & " AND BoxNo >= " & Me("BoxFrom") & ""
End If

If Not IsNull(Me("BoxTo")) Then
strWhere = strWhere & " AND BoxNo <= " &Me("BoxTo") & ""
End If

hopefully this will be the end of my tinkering, i hate vb so much =\