Get previous months data when running in current month

[sushmitha]

My table has a data field from where I am getting data by month

Now my users need to see the data for current month only next month 1st

i.e currently we are in August month. When they run report they should see only Jan till July data

August comes into picture only on Sep1st when they run report

How to set the crateria to look back month and not display current month until it is completed ??

 

[datAdrenaline]

Set your criteria on the appropriat date field to the last day of the previous month, which can be calculated with this expression ...

DateSerial(Year(Date), Month(Date), 0)

 

[raskew]

Try - Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),Month(Date()),0) - as the criteria for your date field.

Should return all records between Jan 1 of the current year and the last day of the previous month.

HTH - Bob

 

[sushmitha]

Try - Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),Month(Date()),0) - as the criteria for your date field.

Should return all records between Jan 1 of the current year and the last day of the previous month.

HTH - Bob

I got the date displayed in Mm/dd/yyyy hh:mm:ss format. But when I convert that field to date/time in table it is throwing an error. It is still taking it as a text. How to convert it to date format. I need to get the most recent date from the whole list of dates

 

[raskew]

It is still taking it as a text. How to convert it to date format. I need to get the most recent date from the whole list of dates

That's a new wrinkle, not part of the original problem.

If, as it sound like, your date is actually a text field, add a date/time field to your table, then run an Update query, updating your date field to DateValue([your text/date field]). Once satisfied with the results, delete the text field and rename the date field.

Bob

 

[sushmitha]

That's a new wrinkle, not part of the original problem.

If, as it sound like, your date is actually a text field, add a date/time field to your table, then run an Update query, updating your date field to DateValue([your text/date field]). Once satisfied with the results, delete the text field and rename the date field.

Bob

Still having problem. I need to convert 8 digit timestamp to 8 digit in access. Will access supports milli sec

 

[raskew]

Rather than reveal the scope of the problem in dribs and drabs, why not lay out the whole thing all at once. Couple of questions:

1) What is the data type of your 'date field'.

2) Please provide some examples of your 8-digit time stamp.

Bob

 

[sushmitha]

Rather than reveal the scope of the problem in dribs and drabs, why not lay out the whole thing all at once. Couple of questions:

1) What is the data type of your 'date field'.

2) Please provide some examples of your 8-digit time stamp.

Bob

1) Text. I tried to convert to Date/Time. But when I update table, it is deleting all records saying there is an error
2)tbl_mbrshp_fulfilmtSTATETM12300905

 

[raskew]

Please return to Post #7.

Bob

 

[sushmitha]

Please return to Post #7.

Bob

How to return to Post #7

 

[raskew]

Scroll up. Look at the number in upper right of the post (next to the little scale).

Which part of tbl_mbrshp_fulfilmtSTATETM12300905 is the 8-digit time stamp? Where's the date?

Bob

 

[sushmitha]

Scroll up. Look at the number in upper right of the post (next to the little scale).

Which part of tbl_mbrshp_fulfilmtSTATETM12300905 is the 8-digit time stamp? Where's the date?

Bob

Date is 20080521 and time is 12300905

 

[raskew]

If you have a text field: "20080521" representing 21-May-2008, to convert to a valid date/time format:

x = "20080521"
y = dateserial(cint(left(x,4)), cint(mid(x,5,2)), cint(right(x,2)))
? y
5/21/2008

'To prove it is date/time data format.
? cdbl
39589

It would probably be helpful to review the following as to how Access stores dates/times: http://support.microsoft.com/kb/q130514/

Re your time stamp. Access cannot display times in more accuracy than integer seconds. I'm not sure what 12300905 represents in terms of seconds. Please describe and let us know your intent. Do you really need the degree of accuracy of milliseconds, or is that just the data you are being provided?

Again, please describe what the 0905 represents.

 

[sushmitha]

If you have a text field: "20080521" representing 21-May-2008, to convert to a valid date/time format:

x = "20080521"
y = dateserial(cint(left(x,4)), cint(mid(x,5,2)), cint(right(x,2)))
? y
5/21/2008

'To prove it is date/time data format.
? cdbl
39589

It would probably be helpful to review the following as to how Access stores dates/times: http://support.microsoft.com/kb/q130514/

Re your time stamp. Access cannot display times in more accuracy than integer seconds. I'm not sure what 12300905 represents in terms of seconds. Please describe and let us know your intent. Do you really need the degree of accuracy of milliseconds, or is that just the data you are being provided?

Again, please describe what the 0905 represents.

Thank you for your reply.

Date and Timestamp we are getting from DB2 database. It is capturing the milli seconds also.
So it 12:30:09 sec and 05 milli seconds

I need to get the max timestamp for each document contro number

 

[raskew]

OK - You can take that timestamp and convert to a valid time.

x = "12300905"
y = "12:30:09"
z = timevalue
? z
12:30:09 PM
? cdbl(z)
0.5209375

What kind of documents are you managing that 5/1000 of a second would make a difference?

Bob

 

[sushmitha]

OK - You can take that timestamp and convert to a valid time.

x = "12300905"
y = "12:30:09"
z = timevalue
? z
12:30:09 PM
? cdbl(z)
0.5209375

What kind of documents are you managing that 5/1000 of a second would make a difference?

Bob

I need to find out the document's most recent status whether it is scheduled,approved or delivered. I can get the recent information based on the timestamp . So within 1/1000 sec also the status can be changed

Does access can hold milli second information ?? If so, how can I define that ??

 

[datAdrenaline]

I find it easier to convert undelimited, fixed length dates using the Format() function ..

? CDate(Format("20080521","0000-00-00"))
5/21/2008

The same for Time ...

? CDate(Format(Left("12300905",6),"00:00:00"))
12:30:09 PM

 

[datAdrenaline]

>> Does access can hold milli second information ?? <<

JET, afaik, does not go to that resolution with a Date/Time field.

You could, of course, store millisecond information ... in a field all by itself if you feel the need to do so.

 

[raskew]

I find it easier to convert undelimited, fixed length dates using the Format() function

Yeah, I agree.

Thanks for that. - Bob

 

[sushmitha]

>> Does access can hold milli second information ?? <<

JET, afaik, does not go to that resolution with a Date/Time field.

You could, of course, store millisecond information ... in a field all by itself if you feel the need to do so.

How to store that information in Ctime column ??
Can you send me the query