date function no result

[tecassitant]

i have one table which hold certain details like date ,name,address etc.now i want to code in such a way if a button is clicked in the form it should give me current date in (mmyyday format i.e its result should be like 020201 this)and it should check for the next date in the date field and if the month of the previous date is the same then the current date field should increment by one that is (if date field as follows then if in the form say
01/01/01 we have created a button like [yes]
02/01/01
19/01/01
30/01/01
11/02/01
19/05/01
if seeing these details in the form and if button [yes] is pressed it should display (after seeing one date [yes] button is pressed and it should give coressponding current date)and this continous till the end of the table)

current date for 01/01/01 if pressed yes display 010101)
02/01/01 010102
19/01/01 010119
30/01/01 010130
11/02/01 020111
19/05/01 050119
that is it should check for the first date in tha date feild and it should return the value as the above and after that it should check for the(date field in next record and see for the month of the previous and year of the previous and display as i typed above.
this is the task can this be done if yes please provide me with nessecary coding s in this.
and one more thing in the form after viewing the date there will be a command button and if that is clicked only it should display the result in the same form or in another form .thats not an issue
any help in this case will help me a lot

thank you
krishnakumar.B

 

[allanon]

What you have done here is text manipulation.

I just did a query but what I did would work just as well in code. Here was my table:

MyField
01/02/03
03/02/01
01/02/02
04/06/00
30/12/02
13/12/01

Here was my query:

SELECT Table1.MyField, mid( Table1.MyField, 4, 2) & mid( Table1.MyField, 7,2) & mid( Table1.MyField, 1, 2) as MyDate
FROM Table1;

And the result:

MyField MyDate
01/02/03 022001
03/02/01 022003
01/02/02 022001
04/06/00 062004
30/12/02 122030
13/12/01 122013

I am not sure if this answers your logic questions because I am not quite sure what it is you need but this will answer you manipulation question.

 

[tecassitant]

thank u for your answere
but i want it in such a way that the caredate(field) and the system date for that field(i.e caredate) should be compared and the system itself should a date when button click in the format mmyyday without any sapce or / or - it should be continous.

bcouz we are going to use this number, we call it as scfr(field) number as a reference for the carenumber ,when it is being submitted and many details as such.
please do provide me some coding for this if this can be done.

thankyou
krishnakumar

 

[KenReay]

Hi

You can present the date without the "/" so

MyString = Format(Date(),"mmyydd&quot

eg

? Format(Date(),"mmyydd&quot
010308
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tecassitant]

hai,

thank you for ur patients but could you please provide me with exact coding if it is used in a table and with fields.
thanking you
krishnakumar.B

 

[allanon]

I am not sure what you are asking for.

 

[tecassitant]

i have a table with fields carenumber,caredate,invoice number
now i want to create a field called scfr
this scfr is used as a reference to refer the abouve details
therefore this number is generated by looking to the caredate field and the systemdate and generated automatically in the format mmyydd (without any space or/ or-).
and after 30days or 31 it should initialize by 01 .
this is my actual query can we do this if yes please provide me with some codings as i am new to access and this task should get completed immediately.

thankyou
krishnakumar.B

 

[tecassitant]

i follow to that(previous)
that particular scfr should be irrespective of the caredate
it should generate like
if there is no carenumber then print current date,month and year
if there is carenumber see the caredate and the system date and print mmyydd the same way increment only the date to 02
and 03 upto 30 or 31 then after end of month initilize that to 01.
again if there is no carenumber print the current mmyydd

and one thing it should be in the format mmyydd without any space or/,-

can we do this then please provide nesscecary coding s that is executable immediatly

thank you
krishnakumar.B

 

[tecassitant]

hi(urgent)
i follow to the previous
now when a carenumber is encountered it should see to the caredate field and system date then it should print currentmonth,year and day
if another carenumber is encountered see the previous scfr and if that scfr has the same month and year then increment the day by +1(and thus the scfr field should have unique values)
this process continous till there is no carenumber
if no carenumber in the carenumber field then print the current month,year and month.
can this be done .if yes then provide me with full codings as i am new to access

thank you
sincerely
krishnakumar.B(urgent)

 

[KenReay]

Hi

So you are trying create a unique key on each row. The unique key is to be a sequence number within care date.

Right?

Why do this at all, why not just have an autonumber field in your table to provide a unique number? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tecassitant]

this is because through that number that is mmyydd that is
for example 120230(this is mmyydd this number cannot be recognized by any one except the user and this number helps the user to know when the claimis being launched through mm
and year

 

[allanon]

What you are asking for is not a simple thing.

You are asking that when a record is added to the table that you use the month and year as part of the key and find the highest day of the month and add 1. This of course is for a single unique carenumber.

Based on this request you are limiting yourself to the number of days in a month for the number of records that you can save for any particular care number.

What you are asking for is not an easy thing. You are asking to do this in a query.

Before I attempt any sort of answer I have to ask to obvious question; why not just add an idiot auto number key to your table? Then you no longer have to worry about incrementing the scfr field. Just create it from the current date as above and the user would still know what date the record was from. That would also not limit you to the number of days in a month for the number of records for a care number. Users always say that there are lots of numbers available and they will never run out but the first time that they have too many records then fixing it becomes a nightmare.

Now to the answer that you are looking for (but maybe not going to like).

You are going to have to do this in code. Before I give you a bunch of stuff that you don't understand here is the basic flow of what would need to happen.

1) determine if a record exists for that care number and that month or greater.

2) if not then add one for the first of the current month.

3) if so then add one where the generated date is one day greater then the most current date on file. To do this you may need to add a field to you table that stores an actual date/time version of the generated number.

These three steps are possible but they are not just a simple query. They will require vb code. For that to happen you will need to determine when and where the code should be executed.

 

[tecassitant]

hi,

thank s for your pateints and reply to my query but cant you not give me codings regarding this becouse i am new to access and vb i will be thankfull to you if you provide me with some nesscescary codings.

 

[allanon]

Here is a routine that will generate the number you want and update a table. In my case Table2. You will need to edit this to use your tables.

Public Function UpdateDate(ByVal MyNumber As Integer, ByVal MyDate As Date) As Boolean
Dim rs, rs2 As DAO.Recordset
Dim strSQL As String
Dim MyDate1, MyDate2 As Date
Dim MyNewField As String

MyDate1 = CDate("01/" & Format(Month(MyDate), "00&quot & "/" & Format(Year(MyDate), "0000&quot)
MyDate2 = CDate(Format(Day(DateAdd("d", -1, DateAdd("m", 1, MyDate))), "00&quot & "/" & Format(Month(MyDate), "00&quot & "/" & Format(Year(MyDate), "0000&quot)
strSQL = &quot;SELECT * FROM Table2 WHERE CareDate >= #&quot; & MyDate1 & &quot;# AND CareDate <= #&quot; & MyDate2 & &quot;#&quot;
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If rs.RecordCount = 0 Then
MyNewField = Format(MyDate, &quot;mmyydd&quot
Else
strSQL = &quot;SELECT MAX(cint(mid(Table2.scfr, 5, 2))) as MaxDay FROM Table2 WHERE CareDate >= #&quot; & MyDate1 & &quot;#&quot;
Set rs2 = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
MyNewField = CDate(Format((rs2!MaxDay + 1), &quot;00&quot & &quot;/&quot; & Format(Month(MyDate), &quot;00&quot & &quot;/&quot; & Format(Year(MyDate), &quot;0000&quot)
MyNewField = Format(MyNewField, &quot;mmyydd&quot
End If
rs.AddNew
rs!CareNumber = MyNumber
rs!Caredate = MyDate
rs!scfr = MyNewField
rs.Update
UpdateDate = True
End Function


Place this code in a module.

I created two text boxes. One for a claim number and one for a date. I create a command button. I placed a claim number in one text box and a date in the.

Place this code in the click event of a button:

Dim dummy As Integer

dummy = UpdateDate(txtNumber, txtDate)

This will allow you to pass whatever dates you need. System date or just a second date. If you need more help I will need to see a demo version of the db.

 

[tecassitant]

hi allanon

thank you sir for your fullest effort i havent tried it yet and i will try this and if i am not getting the result i will ask your help.
thank you for spending your precious time in my question.

thank you
krishnakumar.B

 

[allanon]

Change this line:

strSQL = &quot;SELECT * FROM Table2 WHERE CareDate >= #&quot; & MyDate1 & &quot;# AND CareDate <= #&quot; & MyDate2 & &quot;#&quot;

to be

strSQL = &quot;SELECT * FROM Table2 WHERE CareDate >= #&quot; & MyDate1 & &quot;# AND CareDate <= #&quot; & MyDate2 & &quot;# AND CareNumber = &quot; & MyNumber

Change this line:

strSQL = &quot;SELECT MAX(cint(mid(Table2.scfr, 5, 2))) as MaxDay FROM Table2 WHERE CareDate >= #&quot; & MyDate1 & &quot;#&quot;

to be

strSQL = &quot;SELECT MAX(cint(mid(Table2.scfr, 5, 2))) as MaxDay FROM Table2 WHERE CareDate >= #&quot; & MyDate1 & &quot;# AND CareNumber = &quot; & MyNumber

 

[tecassitant]

i have a dought
when appending records through VBA .
i have one table in foxpro and i want to append these records into access table checking whether the last careno and the caredate field is greater than the foxpro table s
carenumber and caredate field if this condition satisfies then append all the records .that is if there is 100 rec in my access and if there is 180 records in foxpro it checks and it appends only 80 records.can this be done through transferdatabase or ?
any help appreciated

thanking you
krishnakumar.B

 

[tecassitant]

i have a dought
when appending records through VBA .
i have one table in foxpro and i want to append these records into access table checking whether the last careno and the caredate field is greater than the foxpro table s
carenumber and caredate field if this condition satisfies then append all the records .that is if there is 100 rec in my access and if there is 180 records in foxpro it checks and it appends only 80 records.can this be done through transferdatabase or ?
any help appreciated

and allanon i tried your coding and it resulted in overflow
a message called overflow i coming how can we overcome this

thanking you
krishnakumar.B

 

[tecassitant]

hi,

i got the answere for appending.but not for tht overflow message ok
now that i want to create reports on this and there are many tables linked to each other and there are many fields ex:careno,caredate,division,etc
now if enter division and month it should throw me the details that matches those .butremember we dont have a field for month ,instead we have a date field is that possible with the help of these info we can create a query.
if query is created we can create a form based on this isit not.

thanking you
krishnakumar.B

 

[tecassitant]

hi,

please give me a solution for that overflow error
as it has become most urgent for me.

thanking you
krishnakumar.B