Creating search form with SQL

aywho

New member
Local time
Today, 11:03
Joined
Jul 14, 2004
Messages
6
Hi,

I don't know much about VB, and I'm wondering if anyone can help me with its syntax. I have a simple database that keeps track of a "tblDebitNote" table. It has the following attributes:

-DebitNoteID (Autonumber, PK)
-Date (date/time)
-Reference Number (text)
-CD Name (text)

I've created a search form (Please see attachment: screenshot.jpg) so that when the user enters the Date, the ReferenceNumber and click "Search", the CDName can be displayed in the textbox. However, I'm not sure how to do with the SQL statements.

I know I have to create a function called cmdSearch_Click() for the "search" button, and this is the what I have so far:

Private Sub cmdSearch_Click()
Dim strDate As String
Dim strRefNum As String
Dim strSQL As String

strSQL = "SELECT tblDebitNote.CDName FROM tblDebitNote WHERE strDate = tblDebitNote.Date AND strRefNum = tblDebitNote.RefNum"

----don't know what to do next------

End Sub

Is my SQL statement correct? How do I 'call' this SQL statement? How do I get the search results or the text value of the CDName? What is the best way to display the result if there is more than 1?

Really appreciate your time and help.

Gratefully,
aywho
 

Attachments

  • 497447-screenshot.jpg
    497447-screenshot.jpg
    60.8 KB · Views: 153
aywho said:
Is my SQL statement correct?

No.

  • You have the clauses in the WHERE statement the wrong way around;
  • You are trying to compare a literal string to a date field rather than using date delimiters;
  • You have not suffixed the SQL with a semi-colon.

strSQL = "SELECT tblDebitNote.CDName FROM tblDebitNote WHERE tblDebitNote.Date = #" & strDate & "# AND tblDebitNote.RefNum = """ & strRefNum & """;"

Other things wrong are:

  • You have a field called Date which is a reserved word in Access;
  • strDate is a String when it most likely should be a date;
  • You haven't put a value into any of these variables;
  • You are using a table directly rather than using a query.

An even greater problem, however, is that your table structure is flawed.
 
Hi,

Thanks a lot for the reply. I'm wondering if you can help me with the following few questions:

1) When you say I need to put values into the variables, do you mean I need to initialize them to blank value?

2) I'm not familiar with query, is it possible to make this search form work without using query? If using query is a must, what exactly do I need to do?

3) What are the issues of my table? What can I do to improve it?

Thanks a lot
 
aywho said:
1) When you say I need to put values into the variables, do you mean I need to initialize them to blank value?

No. When you create a variable it is initially set at a default value. A string defaults to ""

As you don't have anything in the variable to search on, you are looking for all records where tblDebitNote.Date = "" AND tblDebitNote.RefNum = "" which will, no doubt, return nothing.

I'm not familiar with query, is it possible to make this search form work without using query? If using query is a must, what exactly do I need to do?

Rather than using variables I'd refer to the controls on your form where you are entering your search criteria. i.e. [Forms]![MyForm]![MyTextBox]

There shouldn't be any need to write the query with VBA.

What are the issues of my table? What can I do to improve it?

It isn't just the table you've posted but I'd say, based on that table, the whole database. It's too much for me to go into but I'd suggest doing a search on normalisation - the way to properly design a robust relational database structure.
 

Users who are viewing this thread

Back
Top Bottom