UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Force To Input And Save Date As Dd/mm/yyyy, Access 2007    
 
   
JonSmith
post Oct 11 2018, 02:46 AM
Post#21



Posts: 4,024
Joined: 19-October 10



QUOTE
Thanks everyone for your reply. I could not explain the subject clearly. No matter how the computer ‍saves date, but entry data in the date field in my desired way is essential. I want to entry date as dd/mm/yyyy format. Is there any way please. Sometimes it is giving wrong information. I've set my date field format as dd/mm/yyyy. Now, if I entry 01/05/2018 (01 May 2018) then access converts it as 05 Jan 2018 . Is there any solution please? Otherwise I've to use Text data type for the date field.


Did you read my first response. I went it alot of detail about where your code was incorrect and told you how to go forward and correct it.
In further posts we've all discussed how to handle dd/mm/yyyy formats. If your control is bound then there should be no need to worry about regional settings so you must be saving the date in some other way.

You don't need to use a text data type. The solution you ask for again is already above your post, I'm not going to repeat it so I would ask you to read it again properly and post back with specifics about any parts you find unclear or struggle to implement.
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2018, 07:07 AM
Post#22


UA Admin
Posts: 34,544
Joined: 20-June 02
From: Newcastle, WA


While there is sometimes a problem when language differences are involved, we seem to be dealing with an additional level of problems.

First, Dates are stored in Access differently from strings. I think we all know that, but part of our problem seems to be that we are not actually taking that into account.

Simply concatenating strings together in a specific sequence doesn't turn them into dates.

We, humans, are used to seeing that kind of ambiguous string and interpreting them appropriately.

Computers in general, and Access in particular, are NOT good at resolving ambiguous strings. Access does do a lot to try to help. However, it is not really going to be perfect, at least not in the sense that Access always guesses what YOU intend.

Therefore you must be very explicit in telling Access how to handle input.

You can have three controls on a form, as you want. But you must ALSO tell Access very explicitly how to CONVERT the strings in those controls into a VALID date in the FORMAT you want to see.

The DateSerial() function does that. Concatenation does NOT do that.

At least one previous post pointed out how to use DateSerial to convert the values from your Month control, Day control, and Year control. Use that.

Store the converted date in a date field in the table.

You can FORMAT those dates in many different ways, once you have them stored as dates.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2018, 07:10 AM
Post#23


UA Admin
Posts: 34,544
Joined: 20-June 02
From: Newcastle, WA


A few months ago I created a short YouTube video on dates that may be interesting with regard to FORMATTING.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
mitsbd
post Oct 12 2018, 08:58 AM
Post#24



Posts: 32
Joined: 23-May 18



JonSmith,
I've read all the previous comment. But unfortunately could not solve my problem. I think it's my incapacity. Today I'll try to do it watching the video of GroverParkGeorge.
Thank you all for your help.
Regards.
Go to the top of the page
 
JonSmith
post Oct 12 2018, 09:20 AM
Post#25



Posts: 4,024
Joined: 19-October 10



QUOTE
post back with specifics about any parts you find unclear or struggle to implement.


Do this then?
We cannot help you unless you tell us what you tried. We posted methods we know will work so just hearing you couldn't use them doesn't leave us much info to help you move forward.

Tell us and show us what you tried and what isn't working and I am sure we can work this out.
Go to the top of the page
 
mitsbd
post Jan 4 2019, 01:23 PM
Post#26



Posts: 32
Joined: 23-May 18



Hi,
For my busyness I could not confirm here about this. Unfortunately I could not solve the problem. I've tried all of the steps. But no solution found. Now I want to discuss it step by step. May be it may help me to figure out the problem.

The following code shows run time error 2467

CODE
Private Sub cmbClass_AfterUpdate()
Dim strSQL As String, strWhere As String

  
  If Not IsNull(Me.cmbClass) Then
    strWhere = strWhere & " AND ClassID = " & Me.cmbClass
  End If
  
  If Not IsNull(Me.cmbSection) Then
    strWhere = strWhere & " AND SectionID = " & Me.cmbSection
  End If
  
  If Not IsNull(Me.txtAttDate) Then
       strWhere = strWhere & " AND AttDate = #" & Format(Me.txtAttDate, "dd-mmm-yyyy") & "#"
  End If

  If Len(strWhere) > 0 Then

    strWhere = " WHERE " & Mid(strWhere, Len(" AND "))
  End If
  txtAttDate.Requery

  strSQL = "Select * from qryStuAttendance" & strWhere

  Me!frmStuAttendanceSubform.Form.RecordSource = strSQL
  Forms!frmStuAttendance!frmStuAttendanceSubform.Form.OrderBy = "[StuAttID]"
  Forms!frmStuAttendance!frmStuAttendanceSubform.Form.OrderByOn = True
  
  Me.AttGraphSecNClass.Requery

End Sub


Go to the top of the page
 
BruceM
post Jan 4 2019, 01:49 PM
Post#27


UtterAccess VIP
Posts: 7,814
Joined: 24-May 10
From: Downeast Maine


Which line causes the error? That error number is because the object is closed or does not exist. I would guess it is one of the forms, but I don't know.

The code for the date may work if the language is English. If not, it may work if the three-letter abbreviation for the month is the same as in English, but otherwise it will not:

strWhere & " AND AttDate = #" & Format(Me.txtAttDate, "dd-mmm-yyyy") & "#"

As described earlier, it needs to be either this:

strWhere & " AND AttDate = #" & Format(Me.txtAttDate, "mm/dd/yyyy") & "#"

or:

strWhere & " AND AttDate = #" & Format(Me.txtAttDate, "yyyy/mm/dd") & "#"
Go to the top of the page
 
mitsbd
post Jan 5 2019, 07:24 AM
Post#28



Posts: 32
Joined: 23-May 18



Thanks Bruce,

Your suggestion is perfect for this. But I need to filter my data as dd/mm/yyyy. Unfortunately I could not solve my main problem. If I select '01 January 2019' from the date picker of txtAttDate then it automatically changed to '01 May 2019'. I need to insert data as dd/mm/yyyy. I've tried using previous suggestions even tried as GroverParkGeorge's video. But not solved. I can see my attendance data if I select a date larger then 12. Here I've attached my database with sample data to get advanced suggestion from the experts.

With best regards

Attached File(s)
Attached File  Attendance.zip ( 298.6K )Number of downloads: 6
 
Go to the top of the page
 
mitsbd
post Jan 6 2019, 01:11 PM
Post#29



Posts: 32
Joined: 23-May 18



Hello UA Experts,
As I mentioned in my previous reply that " If I select '01 January 2019' from the date picker of txtAttDate then it automatically changed to '01 May 2019'." Should I use Data Type 'Text' instead of 'Date/Time' for attendance date field? Is there any solution please?
With best regards.
Go to the top of the page
 
JonSmith
post Jan 7 2019, 04:15 AM
Post#30



Posts: 4,024
Joined: 19-October 10



Hi,

I tried your download and using the date picker I selected 01 January 2019 aswell as many other dates, the dates all displayed correctly.

You need to start answering the questions answered by the posters here rather than asking new ones yourself each time. For example BruceM was asking what line caused the error as it seems unrelated to the date stuff and we didn't get a reply.

As I have said many times, no no no do you need to use a text field instead of a date field. It will be much much worse. You just need to start working with dates in the structured way myself and many others have explained. You seem to be missing that dates are not stored in dd/mm/yyyy format or mm/dd/yyyy format. Dates are just dates and can be displayed in dd/mm/yyyy or mm/dd/yyyy.

When Bruce talks about using yyyy/mm/dd in your SQL that has no affect on how you must display the dates. It is simple the most robust format used when writing a date in SQL as it is region independent.
You must keep the date picker as a date and keep the data stored as a date, take the value of the datepicker and format as yyyy/mm/dd in the code you use the write your SQL, the display format is irrelevant.


If the behaviour you describe where the datepicker keeps changing then please use psr.exe (search in the start menu) and record the issue for us.
My gut says that if you try today its going to switch 01-Jan-2019 to 01-Jul-2019 as the issue here is related to your 'default' value which should just be Now() and not =Format(Day(Now()),'00') & "/" & Format(Month(Now()),'00') & "/" & Format(Year(Now()),'00').
Would I be correct in assuming you have system settings of mm-dd-yyyy?
Go to the top of the page
 
BruceM
post Jan 7 2019, 07:30 AM
Post#31


UtterAccess VIP
Posts: 7,814
Joined: 24-May 10
From: Downeast Maine


QUOTE
But I need to filter my data as dd/mm/yyyy.

I can only repeat: No, you don't. If you explain why you believe you need to do that, it will be possible to show how to do it with date fields. Even if you want to filter by the day of the month, rather than by year or date or specific date or month/year combination, you can (and should) work with date fields.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    18th February 2019 - 05:34 AM