Where is my codecerror?

N

Nova

This is my code in btnSearch_click event


Dim Cnx As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim StrRst As String
Dim N As Integer


StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=Forms!MatTurnoverF.StartDate);"

Set Cnx = CurrentProject.Connection
Set Rst = New ADODB.Recordset


Rst.Open StrRst, Cnx, adOpenForwardOnly, adLockReadOnly, 1

It show message

No value given for one or morerequired parameter

If I copy Strrst to create query, it work correct. I don't understand where
am I wrong?
 
J

John Spencer

When working in VBA the reference to the control on a form is NOT recognized.
The simplest was to handle the situation is to grab the value of the control
and concatenate it into the query string.

IF IsDate(Forms!MatTurnoverF!StartDate) Then
StrRst = "SELECT * FROM MatStock WHERE [ActionDate]=" &
FORMAT(Forms!MatTurnoverF!StartDate,"\#yyyy-mm-dd\#")

'Your other code here
Else
Msgbox "Please enter a valid Start Date."
End If


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
T

Tom van Stiphout

On Mon, 11 Jan 2010 02:50:01 -0800, Nova

I would write that as:
StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=#" & Forms!MatTurnoverF!StartDate & "#);"

or better still, put the code in a query.

-Tom.
Microsoft Access MVP
 
N

Nova

I try it many times but it still has some error

StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=#" & Forms!MatTurnoverF!StartDate & "#);"

I think may be mydate is Thailand format not US format
Then I change code to

StrRst = "SELECT * FROM MatStock WHERE
(Format([ActionDate],""mmm yy"")=Format(#" & Forms!MatTurnoverF!StartDate &
"#,""mmm yy"");"
also I change ate time format in regional setting to Thai,The result is no
error like before but The Rst is no any record

then I try change coe to

(Format([ActionDate],""mmm yy"") Between
Format(#" & Forms!MatTurnoverF!StartDate & "#,""mmm yy"")
AND Format(#" & Forms!MatTurnoverF!EndDate & "#,""mmm yy""))

It still no error but the recordset from Rst is not correct, some recoed is
outsie startdate and enddate, some record between startdate and enddate is
not shown
How should I do?

"Tom van Stiphout" เขียน:


On Mon, 11 Jan 2010 02:50:01 -0800, Nova

I would write that as:
StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=#" & Forms!MatTurnoverF!StartDate & "#);"

or better still, put the code in a query.

-Tom.
Microsoft Access MVP

This is my code in btnSearch_click event


Dim Cnx As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim StrRst As String
Dim N As Integer


StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=Forms!MatTurnoverF.StartDate);"

Set Cnx = CurrentProject.Connection
Set Rst = New ADODB.Recordset


Rst.Open StrRst, Cnx, adOpenForwardOnly, adLockReadOnly, 1

It show message

No value given for one or morerequired parameter

If I copy Strrst to create query, it work correct. I don't understand where
am I wrong?
Click to expand...
.
Click to expand...
 
J

John W. Vinson

I try it many times but it still has some error

StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=#" & Forms!MatTurnoverF!StartDate & "#);"

I think may be mydate is Thailand format not US format
Then I change code to
Click to expand...

What is Thailand format? Could you post today's date as an example?
StrRst = "SELECT * FROM MatStock WHERE
(Format([ActionDate],""mmm yy"")=Format(#" & Forms!MatTurnoverF!StartDate &
"#,""mmm yy"");"
Click to expand...

mmm yy is not a date: it must be a complete date. In addition, the Format()
function returns a text string, and your # is converting the criterion to a
Date/Time.

Try

strRST = "SELECT * FROM MatStock WHERE ActionDate = #" & _
Format(CDate([Forms]![MatTurnoverF!StartDate), "yyyy-mm-dd")) & "#"
 
N

Nova

I try as you reccommend but it still error
Thai format similar US format but the year is 543 B.C.
Today is 14 Thaimonth 2553

then I try
Rst.Open "MatStock", Cnx, adOpenForwardOnly, adLockReadOnly, -1
N=0
With Rst
do until .EOF
If StartDate = .Fields!ActionDate.Value Then

MgBox .Fields!ActionDate.Value
N=N+1
End If
.MoveNext
Loop
End With
Msgbox N
Rst.close
Set Rst = Nothing

It works correct but this must check all records in table "Matstock"
Please help me again


"John W. Vinson" เขียน:
I try it many times but it still has some error

StrRst = "SELECT * FROM MatStock WHERE
([ActionDate]=#" & Forms!MatTurnoverF!StartDate & "#);"

I think may be mydate is Thailand format not US format
Then I change code to
Click to expand...

What is Thailand format? Could you post today's date as an example?
StrRst = "SELECT * FROM MatStock WHERE
(Format([ActionDate],""mmm yy"")=Format(#" & Forms!MatTurnoverF!StartDate &
"#,""mmm yy"");"
Click to expand...

mmm yy is not a date: it must be a complete date. In addition, the Format()
function returns a text string, and your # is converting the criterion to a
Date/Time.

Try

strRST = "SELECT * FROM MatStock WHERE ActionDate = #" & _
Format(CDate([Forms]![MatTurnoverF!StartDate), "yyyy-mm-dd")) & "#"
Click to expand...
 
J

John W. Vinson

I try as you reccommend but it still error
Thai format similar US format but the year is 543 B.C.
Today is 14 Thaimonth 2553
Click to expand...

I'm sorry, but I simply do not understand. "Thaimonth"? Are these lunar
months, Gregorian calendar months with different names, ...?
then I try
Rst.Open "MatStock", Cnx, adOpenForwardOnly, adLockReadOnly, -1
N=0
With Rst
do until .EOF
If StartDate = .Fields!ActionDate.Value Then

MgBox .Fields!ActionDate.Value
N=N+1
End If
.MoveNext
Loop
End With
Msgbox N
Rst.close
Set Rst = Nothing

It works correct but this must check all records in table "Matstock"
Please help me again
Click to expand...

There is NO need to use code or a recordset to find all records in the table
with StartDate equal to ActionDate, or to count them. You're using a database!
Use a query!

SELECT ActionDate, <any other fields you want> FROM MatStock WHERE ActionDate
= Me.StartDate;

will display all the records matching the date.

Perhaps you could explain some more about the context. What's in the table?
What are you trying to accomplish?
 
N

Nova

Thaimonth is month in thai language (Like Jan,Feb,....,Dec in Thai language)

I try

StrRst = "SELECT * FROM Matstock WHERE (Format([ActionDate], ""dd mmm yy"")
=Format('" & [Forms]![MatTurnoverF].[StartDate] & "', ""dd mmm yy""))"

It show me correct record

and then I change from = to between

StrRst = "SELECT * FROM Matstock WHERE Format([ACtionDate], ""dd mm yyyy"")
Between Format(('" & [Forms]![MatTurnoverF].[StartDate] & "' ), ""dd mm
yyyy"") "
AND Format(('" & [Forms]![MatTurnoverF].[EndDate] & "'), ""dd mm yyyy"")"

The result is not correct again.

field Actiondate in table "Matstock" is set format to dd mm yy
and in form "MaturnoverF", Both Startdate and EndDate combobox is set format
to dd mm yy


"John W. Vinson" เขียน:
 
N

Nova

Thanks for all help



"KenSheridan via AccessMonster.com" เขียน:
That won't work because the 'dd mm yyyy' format does not sort correctly.
Using the same approach try this:

StrRst = "SELECT * FROM Matstock WHERE " & _
"Format([ActionDate], ""yyyymmdd"") Between " & _
Format([Forms]![MatTurnoverF].[StartDate], "yyyymmdd") & " AND " & _
Format([Forms]![MatTurnoverF].[EndDate], "yyyymmdd")

Check first in the debug window that formatting a date entered as a string in
Thai date format does give you the correct date in yyyymmdd format by
entering:

? Format("14 makarakhom 2553","yyyymmdd")

which should give 20100114

Hope I got the month right!

Ken Sheridan
Stafford, England
Thaimonth is month in thai language (Like Jan,Feb,....,Dec in Thai language)

I try

StrRst = "SELECT * FROM Matstock WHERE (Format([ActionDate], ""dd mmm yy"")
=Format('" & [Forms]![MatTurnoverF].[StartDate] & "', ""dd mmm yy""))"

It show me correct record

and then I change from = to between

StrRst = "SELECT * FROM Matstock WHERE Format([ACtionDate], ""dd mm yyyy"")
Between Format(('" & [Forms]![MatTurnoverF].[StartDate] & "' ), ""dd mm
yyyy"") "
AND Format(('" & [Forms]![MatTurnoverF].[EndDate] & "'), ""dd mm yyyy"")"

The result is not correct again.

field Actiondate in table "Matstock" is set format to dd mm yy
and in form "MaturnoverF", Both Startdate and EndDate combobox is set format
to dd mm yy

"John W. Vinson" เขียน:
I try as you reccommend but it still error
Thai format similar US format but the year is 543 B.C.
Click to expand...
[quoted text clipped - 34 lines]
Perhaps you could explain some more about the context. What's in the table?
What are you trying to accomplish?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cannot open recordset 2
How to update MsDataShape/NEW field in forms 0
Import multiple Access Queries 0
Access to Excel format 2
Populating a recordset - Can someone please tell me what I'm doing wrong? 2
Update a field in a table 3
vba runtime error 3
object variable or with variable not set 3

Top