Got Stuck with VBA+SQL.....(panicking, please help~)

[aywho]

Hi there,

I've been working on this simple DB for two weeks, but it's still far from completion. I had never done anything with Access+VBA+SQL before, so I'm wondering if anyone can help.

This database stores the information of the 15 type of documents (i.e. Voucher, Invoice...), and provides a search form for the user to search for the CD Name of certain document with different criteria.

These are the problems I encountered:

1) Don't know how to compare a texbox value with a date from a table
strSQL = "SELECT * " & _
"FROM tblDocuments " & _
"WHERE tblDocuments.[DocRefNum] ='" & refEntry.Value & "' " & _
"AND tblDocuments.[DocDate] = '" & dateEntry.Value & "';"

(refEntry & dateEntry are text boxes for users to type in values)


2) I have a subform to display the results, but it never displays anything (I put these two lines below the above SQL statement):

Me.resultForm.Form.RecordSource = strSQL​

Me.resultForm.Form.Requery​

3) I don't know much about Queries, and I just followed other people's sample code and added one query to my DB. However, it seems that it doesn't do much there. What kind of queries should I add to my DB in order to have the search form work?

I'm really hoping that you can kindly take a look at my DB (unfortunately, my file size is a bit too big, 140kb), and give me some guidence. I'm really stuck, and I don't know how to resolve my problems.
I really appreciate your help..

Gratefully.

 

[ReAn]

1) Dates are stored in the database simply as a number, the x amount of days from some date way back that i cannot remember (sorry, cant grab it off the top of my heat at the moment) so, what this means is you can treat dates as a number (sorta).

You can do greater than (>), less than (<), equals (=), between operations on dates.

The key is making sure your date is formatted correctly. When inputting a date into SQL, you can just enter it like a string.

aka:

SELECT * FROM MyTable WHERE MyTable.DateField = '7/20/2004'

2)

Try this:

strSQL = "SELECT * FROM tblDocuments WHERE tblDocuments.[DocRefNum] = [Forms]![frmMyForm].[refEntry] AND tblDocuments.[DocDate] = [Forms]![frmMyForm].[dateEntry]"

Then when you do the requery it should pull the approriate information.

3) Basically build a query like the one in #2 but with all the search fields you want. Then make the form's datasource that query. Then every time you requery the form, it'll have searched for the specific criteria.

 

[aywho]

Thanks a lot for the reply, but I'm still unsure about (3)

3) Do you have any example on how to do this? I'm not very familiar with queries. Can you help me with that?

Thanks a lot

 

[ReAn]

Try this, it should help you out.