Date Comparision

[bangaram via AccessMonster.com]

Hi,
I am not getting the records matched with the entered date. Its raising an
error like
"No recoreds found for that date"

Query:
Me.RecordSource=select Distinct(MeasurementData.Inspector),MeasurementData.
ReadingDate,MeasurementData.Comments
FROM MeasurementData
WHERE MeasurementData.ReadingDate=" & mDate

Is my query wrong or something i need to do..?

Can anyone help me?

 

[Rick B]

what is " & mDate

Is mDate a field in your tables? If so, which table?

maybe...

WHERE MeasurementData.ReadingDate=MeasurementData.mDate

 

[Ken Sheridan]

Date literals in Access must be in US date format or otherwise
internationally unambiguous so its best to format the value of the mDate
variable:

Me.RecordSource="SELECT DISTINCT " & _
"Inspector, ReadingDate, Comments " & _
"FROM MeasurementData " & _
"WHERE MeasurementData.ReadingDate=#" & _
Format(mDate,"mm/dd/yyyy") & "#"

Ken Sheridan
Stafford, England

 

[bangaram via AccessMonster.com]

mDate is a Date variable assigned as follows
dim mDate as Date
mDate=Format(inputbox("enter date;"),"mm/dd/yyyy");

what could be the problem?

what is " & mDate

Is mDate a field in your tables? If so, which table?

maybe...

WHERE MeasurementData.ReadingDate=MeasurementData.mDate

Hi,
I am not getting the records matched with the entered date. Its raising

[quoted text clipped - 12 lines]

Can anyone help me?

 

[bangaram via AccessMonster.com]

Before querying i formatted the mDate variable as u told..... But i didn't
put # symbol in the query.... Is it required ?

Date literals in Access must be in US date format or otherwise
internationally unambiguous so its best to format the value of the mDate
variable:

Me.RecordSource="SELECT DISTINCT " & _
"Inspector, ReadingDate, Comments " & _
"FROM MeasurementData " & _
"WHERE MeasurementData.ReadingDate=#" & _
Format(mDate,"mm/dd/yyyy") & "#"

Ken Sheridan
Stafford, England

Hi,
I am not getting the records matched with the entered date. Its raising an

[quoted text clipped - 10 lines]

Can anyone help me?

 

[Ken Sheridan]

Yes. The # character is the date delimiter character. When you build an SQL
statement in code and concatenate a date/time value into it you need to wrap
the value in # characters. This identifies it as a literal date, which, as I
said, must be in a US format. This has always been the case in SQL, but
strangely this requirement was only introduced in VBA in Access 95. Prior to
that the local regional date format was respected in VBA. Now, however, its
standardised in both VBA and SQL, so you need to do the same if building an
expression as the criterion for a DLookup function for instance.

If you were to omit the date delimiter characters the date would probably be
interpreted as an arithmetical expression, so today, 03/04/2006 would
evaluate to 0.000664672648720505. Now because Access stores date/time values
as a 64 bit floating point number as an offset from 30 December 1899 00:00:00
this would then be evaluated as 30 December 1899 00:00:57, which is unlikely
to match anything in the database!

Ken Sheridan
Stafford, England

Before querying i formatted the mDate variable as u told..... But i didn't
put # symbol in the query.... Is it required ?

Date literals in Access must be in US date format or otherwise
internationally unambiguous so its best to format the value of the mDate
variable:

Me.RecordSource="SELECT DISTINCT " & _
"Inspector, ReadingDate, Comments " & _
"FROM MeasurementData " & _
"WHERE MeasurementData.ReadingDate=#" & _
Format(mDate,"mm/dd/yyyy") & "#"

Ken Sheridan
Stafford, England

Hi,
I am not getting the records matched with the entered date. Its raising an

[quoted text clipped - 10 lines]

Can anyone help me?

 

[bangaram via AccessMonster.com]

Hi Ken,
Thanks a lot for explaining regarding date comparision... it wil help me a
lot... i had one more query related doubt. Can u help me in that also..?

Query: Insert Operation.
insert into MeasurementData
values('" & Form_frmGenerator.selPartNum & "','" & Me.Controls("locLabel" &
ind).Caption & "'," & Round(Val(Me.Controls("txtVal" & ind)), 3) & ",#" &
Format(ReadingDate, "mm/dd/yyyy") & "#,'" & Inspector & "'," & ShiftID.
ListIndex + 1 & "," & Check.ItemData(Check.ListIndex) & "," & sFlag & ",'" &
Comments & "')"

i am getting run time error as "Too few parameters are required". But i kept
all parameters. i verified by priniting the query also. query was correct. i
don't know why i am getting this run time error.. Last parameter of the query
i.e. Comments field can be empty. May be this could be the problem..? If that
is the case how can i overcome that problem?

Yes. The # character is the date delimiter character. When you build an SQL
statement in code and concatenate a date/time value into it you need to wrap
the value in # characters. This identifies it as a literal date, which, as I
said, must be in a US format. This has always been the case in SQL, but
strangely this requirement was only introduced in VBA in Access 95. Prior to
that the local regional date format was respected in VBA. Now, however, its
standardised in both VBA and SQL, so you need to do the same if building an
expression as the criterion for a DLookup function for instance.

If you were to omit the date delimiter characters the date would probably be
interpreted as an arithmetical expression, so today, 03/04/2006 would
evaluate to 0.000664672648720505. Now because Access stores date/time values
as a 64 bit floating point number as an offset from 30 December 1899 00:00:00
this would then be evaluated as 30 December 1899 00:00:57, which is unlikely
to match anything in the database!

Ken Sheridan
Stafford, England

Before querying i formatted the mDate variable as u told..... But i didn't
put # symbol in the query.... Is it required ?

[quoted text clipped - 17 lines]

 

[Ken Sheridan]

You don't seem to have a parenthesised column list after the table name.

Ken Sheridan
Stafford, England

 

[bangaram via AccessMonster.com]

There are no Parenthesised columns in my query. i am forming the query and
storing it into a String named as strQuery like below.

strQuery="insert into MeasurementData values('" & Form_frmGenerator.
selPartNum & "','" & Me.Controls("locLabel" & ind).Caption & "'," & Round(Val
(Me.Controls("txtVal" & ind)), 3) & ",#" & Format(ReadingDate, "mm/dd/yyyy")
& "#,'" & Inspector & "'," & ShiftID.ListIndex + 1 & "," & Check.ItemData
(Check.ListIndex) & "," & sFlag & ",'" & Comments & "')"

currentDB.execute(strQuery) --- Here i am getting run time error.....

You don't seem to have a parenthesised column list after the table name.

Ken Sheridan
Stafford, England

Hi Ken,
Thanks a lot for explaining regarding date comparision... it wil help me a

[quoted text clipped - 13 lines]

i.e. Comments field can be empty. May be this could be the problem..? If that
is the case how can i overcome that problem?

 

[Ken Sheridan]

Are you inserting values into every field in the table? If not you need to
list the columns which the values are to be inserted into in parentheses
after the table name and before the VALUES keyword. What sometimes confuses
people with this is that any autonumber column is included. Normally you
would not assign a value to such a column of course, so no value would be
included in the VALUES list, but in that case you need to list all the other
columns to which the values are to be assigned.

If in the VALUES list you are assigning values to every column then I have
to admit that at present I can't see where the problem might be.

What happens if you copy the string to which the expression evaluates and
paste it into a blank query in SQL view and then run it? Does it then
successfully append a row or not?

Ken Sheridan
Stafford, England

There are no Parenthesised columns in my query. i am forming the query and
storing it into a String named as strQuery like below.

strQuery="insert into MeasurementData values('" & Form_frmGenerator.
selPartNum & "','" & Me.Controls("locLabel" & ind).Caption & "'," & Round(Val
(Me.Controls("txtVal" & ind)), 3) & ",#" & Format(ReadingDate, "mm/dd/yyyy")
& "#,'" & Inspector & "'," & ShiftID.ListIndex + 1 & "," & Check.ItemData
(Check.ListIndex) & "," & sFlag & ",'" & Comments & "')"

currentDB.execute(strQuery) --- Here i am getting run time error.....

You don't seem to have a parenthesised column list after the table name.

Ken Sheridan
Stafford, England

Hi Ken,
Thanks a lot for explaining regarding date comparision... it wil help me a

[quoted text clipped - 13 lines]

i.e. Comments field can be empty. May be this could be the problem..? If that
is the case how can i overcome that problem?

 

[bangaram via AccessMonster.com]

i need to assign value for every field in the table.i am able to append the
record from SQL View. then what could be the problem?

Are you inserting values into every field in the table? If not you need to
list the columns which the values are to be inserted into in parentheses
after the table name and before the VALUES keyword. What sometimes confuses
people with this is that any autonumber column is included. Normally you
would not assign a value to such a column of course, so no value would be
included in the VALUES list, but in that case you need to list all the other
columns to which the values are to be assigned.

If in the VALUES list you are assigning values to every column then I have
to admit that at present I can't see where the problem might be.

What happens if you copy the string to which the expression evaluates and
paste it into a blank query in SQL view and then run it? Does it then
successfully append a row or not?

Ken Sheridan
Stafford, England

There are no Parenthesised columns in my query. i am forming the query and
storing it into a String named as strQuery like below.

[quoted text clipped - 17 lines]

 

[bangaram via AccessMonster.com]

Hi Ken
i got it.. Thanks for the help....

Can u help me in organising tables... i am not getting the possible answers
for that.....
u can go through the link below for that mapping problem
http://www.accessmonster.com/Uwe/Fo...rted/14198/How-to-map-tables#5d36ad4d89c21uwe

i need to assign value for every field in the table.i am able to append the
record from SQL View. then what could be the problem?

Are you inserting values into every field in the table? If not you need to
list the columns which the values are to be inserted into in parentheses

[quoted text clipped - 19 lines]