[help] Correct syntax for the WHERE by date

Question

0.00/5 (No votes)

See more:

VS2010

This is the part that I dont know the syntax
I want that my form automatically loads the data from the Database
by using the b_date but only display the records if there is a match in month and date
not using the year, The program is like a Birthday Reminder

VB

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Bene_dbDataSet.bene_records' table. You can move, or remove it, as needed.
        Me.Bene_recordsTableAdapter.Fill(Me.Bene_dbDataSet.bene_records)

        con = New OleDb.OleDbConnection
        dbProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\Users\RedemptorisMater\Desktop\S\K..\WindowsApplication1\WindowsApplication1\bene_db.mdb"

        If Not con.State = ConnectionState.Open Then
            con.Open()
        End If

        Dim da As OleDb.OleDbDataAdapter
        Dim Sql As String

        ''THIS IS THE PART THAT I DONT KNOW THE SYNTAX,
        ''I want that my form automatically loads the data from the Database
        ''by using the b_date but only display the records if theres a match in month and date
        ''not using the year, The program is like a Birthday Reminder
        Sql = "SELECT * FROM bene_records"
        da = New OleDb.OleDbDataAdapter(Sql, con)

        Dim dt As New DataTable
        da.Fill(dt)

        con.Close()

    End Sub

Posted 30-Oct-14 21:22pm

Member 11194404

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-10-30T22:08:00

Solution 2

Please, read my comment to the solution 1

I'd suggest query like this:

SQL

PARAMETERS [mnth] INT;
SELECT <FieldList>
FROM bene_records
WHERE b_date BETWEEN #DateSerial(Year(Date()), [mnth], 1)# AND #DateSerial(Year(Date()), [mnth]+1, 1-1)#

Above query should return data for given month (from first to last day).
Replace <FieldList> with set of columns you want to return. Do not use * together with SELECT statement, if you care about performance.

To call this, you need to set OledbParameter[^] for OledbCommand[^].

More:
DateSerial (MS Access query)[^]
Data Types (MS Access)[^]

Posted 30-Oct-14 22:08pm

Maciej Los

Updated 30-Oct-14 22:22pm

v3

Comments

Member 11194404 31-Oct-14 8:54am

is this right?

Sql = "SELECT f_name, m_name, l_name, b_date, home_num, cell_num, addr FROM bene_records WHERE b_date between #DateSerial(Year(Date()), [mnth], 1)# AND #DateSerial(Year(Date()), [mnth]+1, 1-1)#"
da = New OleDb.OleDbDataAdapter(Sql, con)

Dim dt As New DataTable
da.Fill(dt)

con.Close()

Maciej Los 31-Oct-14 8:56am

No. Please use entire query body.

Member 11194404 31-Oct-14 9:00am

please enlighten me.. kinda really new on this coding stuff... =/

Maciej Los 31-Oct-14 9:04am

2 errors:
1) You missed PARAMETERS...,
2) A parameter for OleDbCommand hasn't been specified!
Please, read my answer carefully...

Member 11194404 31-Oct-14 9:21am

is there any simple code just to fill the DataTableAdapter with the records of those who have equal to the date (Month and Date only) with the Month and Date of the PC.. excluding the year.. coz its like a birthday reminder program

OriginalGriff · Answer 2 · 2014-10-30T21:49:00

Solution 1

Try:

SQL

SELECT * FROM bene_records WHERE DATEPART(mm, GETDATE()) = DATEPART(mm, dateColumn) AND DATEPART(dd, GETDATE()) = DATEPART(dd, dateColumn)

Posted 30-Oct-14 21:49pm

OriginalGriff

Comments

Member 11194404 31-Oct-14 3:57am

i tried this but there was error (Syntax error in FROM clause.)

Sql = "SELECT * FROM bene_records DATEPART(mm, GETDATE()) = DATEPART(mm, b_date) and DATEPART(dd, GETDATE()) = DATEPART(dd, dateColumn)"
da = New OleDb.OleDbDataAdapter(Sql, con)

Dim dt As New DataTable
da.Fill(dt)

Maciej Los 31-Oct-14 4:09am

MS Access does not know GETDATE() function!