Select a date given in a form

Ben_Entrew

Registered User.
Local time
Today, 08:13
Joined
Dec 3, 2013
Messages
177
Hi all,

I want to select a subset of table called TRP. It should extract me all records

which got the property Valid to date < enddate.

enddate is a date selected by the user in a form .

Somehow it doesn't recognize the enddate in the following :

Code:
Public Sub test()

DoCmd.SetWarnings False

DoCmd.RunSQL "SELECT TRP.* INTO [TEST] " & _
             " FROM TRP " & _
             " Where TRP.[Valid to]  < enddate"
             
DoCmd.SetWarnings True

             
End Sub

Your help is much appreciated.

Thanks in advance.

Regards,

Ben
 
You have to concatenate with delimiters:

" Where TRP.[Valid to] < #" & Me.enddate & "#"

Presuming this code is in the same form.
 
if you remove the setwarnings temporarily, Access will tell you what the error is - which among other things is the last line needs to be

Code:
" Where TRP.[Valid to]  < #" me.enddate & "#"

HOWEVER, runsql (like docmd.execute) runs an action query such as update, insert, delete, it won't run a select query, so your code will fail anyway

To open a select query you need to use the docmd.openquery to run an existing query or use currentdb.openrecordset to create a recordset.

Since you are testing at the moment, it is difficult to suggest what is the right way to go, so you need to be more specifc about what you are trying to achieve.
 
Hi Paul,

thanks so far.

It still shows me the warning message:

Inappropriate usage of keyword Me


Do I have to declare something before I can use this Me. ...?
 
HOWEVER, runsql (like docmd.execute) runs an action query such as update, insert, delete, it won't run a select query, so your code will fail anyway

To open a select query you need to use the docmd.openquery to run an existing query or use currentdb.openrecordset to create a recordset.

Since you are testing at the moment, it is difficult to suggest what is the right way to go, so you need to be more specifc about what you are trying to achieve.

CJ, check the SQL again. ;)
 
If the code isn't behind the form, you need the full form reference.
 
Paul,

I will use two different forms one for startdate and the other for enddate.

How will it look like with the full form property?
 
Basically I want the user to decide which timeframe of data he wants to use later on.
 
I've tried somethings from the list you provided me.
My main form is called TEST. The enddate is selected in a text field appearing in the form Test.

I tried:
Test.enddate
Test.control.enddate.

Nothing worked :(
 
I've added this in the form code:

Code:
Public Sub enddate_AfterUpdate()

Dim a As Date

a = Me.enddate


End Sub

And then tried this in the program module:


Code:
Option Compare Database
Public a As Date

 Public Sub test()
    DoCmd.SetWarnings False
    
   
    DoCmd.RunSQL "SELECT TRP.* INTO [TEST] " & _
                 " FROM TRP " & _
                 " Where TRP.[Valid to] < #" & a & "#"
                 
    DoCmd.SetWarnings True
                 
    End Sub


Now it runs through but doesn't show anything in the new TEST table,

although there are some records with Valid to < enddate.
 
The "Forms!" part is necessary in the full syntax. In the second, you've declared a twice. You don't want it in the first bit.
 
Thanks Paul,

for the hint with Optıon explıcıt.I've done that now.
However it still doesn't work out.

I trıed this :

Code:
 " WHERE Where TRP.[Valid to] < #" & Forms!enddate & "#"

Can you help me with the syntax?
 
Now you've left out the form name.
 
Can I use the variable a ?
I really don't know how to the syntax should look like.
 
It looks like

Forms!test.enddate
 
If you are not getting data back, the date you are entering is it in US date format??

Any "date" inserted into SQL this way needs to be in US Format, MM/DD/YYYY

Generally speaking it is a good idea to do something alike:
Code:
    Dim strSQL as string
    strSQL = "SELECT TRP.* INTO [TEST] " & _
                 " FROM TRP " & _
                 " Where TRP.[Valid to] < #" & a & "#"
    debug.print strSQL
    DoCmd.RunSQL strSQL
Now you can find your SQL in the immediate window so you can easily debug it.
 
You are rigtht namliam,

the date has another format, it's like 23.05.2013.
I tried this, without success :(

Code:
" Where [Valid to] < " & Format(a, "\#mm\/dd\/yyyy\#")

It shows me in the debug window:
SELECT TRP.* INTO [TEST] FROM TRP Where [Valid to] < #12/30/1899#


Although enddate is 15.05.2013 selected.
 

Users who are viewing this thread

Back
Top Bottom