Format() function not changing date variable format or literal code

[lacampeona]

I have a small problem here you experts. I live in Europe and our date format is supposed to be ""04-07-2020"" for July 4th, 2020 and my code, no matter how I write it, is returning the format ""4. 07. 2020"". Here is an example of what I tried first:

Dim dtePwdDate As Date
If intPasswordExpireDays > 0 Then dtePwdDate = Format(Date, "dd-mm-yyyy")

    If dtePwdDate <> 0 Then
        CurrentDb.Execute "INSERT INTO tblUsers ( username, PWDDate )" & _
            " SELECT '" & strUserName & "' AS UserName, " & " " & dtePwdDate & " AS PWDDate"
   end if

and when I try to run that code, it breaks on "CurrentDb.Execute" giving me a syntax error. However, if I do what I think VBA wants me to do with hash marks, like this:

    If dtePwdDate <> 0 Then
        CurrentDb.Execute "INSERT INTO tblUsers ( username, PWDDate )" & _
            " SELECT '" & strUserName & "' AS UserName, #" & dtePwdDate & "# AS PWDDate"
   end if

it gives me a different syntax error and the message shows the date variable as this: 4 .07. 2020

can anyone help me figure out what I am doing wrong? I have even tried putting Format(Date, "dd-mm-yyyy") in the actual SQL statement, and that fails too. Is it a regional setting that is the problem here? I have heard that this has been an issue for some users but I'm a little stuck. Please help. thanks.

 

[theDBguy]

Hi. Try using Format(DateVariable, "yyyy-mm-dd").

 

[plog]

I think its your sql, not your formatting. Here's a link to an INSERT query:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

If you are simply putting values into your table, you don't do a SELECT within the INSERT:

INSERT INTO tablename (field1name, field2name) VALUES ('A', 'B')

 

[lacampeona]

I understand that I am not doing this correctly, but what do you think of these images? This is what is giving me issues. what changes can I make first? I know I have redundant code in here. thank you.

 

[namliam]

your date is being stored inside the database and the default format setting of the date field is US date format.

If you want to have a different date format, you have to apply said format to your display(s) either on table design level or on form/report level.

 

[isladogs]

When using dates in SQL statements, these must be in the format mm/dd/yyyy or yyyy-mm-dd.
Both INSERT INTO...SELECT and INSERT INTO...VALUES are valid approaches for appending data.
Try this

Dim dtePwdDate As Date
If intPasswordExpireDays > 0 Then dtePwdDate = Format(Date, "mm/dd/yyyy")

    If dtePwdDate <> 0 Then
        CurrentDb.Execute "INSERT INTO tblUsers ( username, PWDDate )" & _
            " SELECT '" & strUserName & "' AS UserName, #" & dtePwdDate & "# AS PWDDate;"
   End if

 

[lacampeona]

Hello
I still get the error

hmm
what i am doing wrong?

 

[isladogs]

What was the code that you used this time?

EDIT
Try commenting out the If and End If lines

 

[lacampeona]

hi
i used your code that you send.
ok i will try
thanks

 

[June7]

See if this helps http://allenbrowne.com/ser-36.html

 

[CJ_London]

confused as to whether dtePwdDate is a date field, a control or a variable. you are setting dtePwdDate as format of itself, which does not make sense. the format function creates a string - then your code says 'if dtePwdDate<>0 then' which implies it is a number

And having already formatted as text, you do it again in the sql code

 

[lacampeona]

Hello experts..i am at home again....i will try your solutions ..i hope i will get it

thanks in advance to all of you

 

[Pat Hartman]

Whenever you create SQL queries as strings in VBA AND your default date format is NOT mm/dd/yyyy, you MUST ALWAYS use the Format() function to format variable dates in that string as mm/dd/yyyy unless you prefer yyyy/mm/dd.

The default date format in Windows defines how dates are displayed in all places unless they are specifically formatted. Have you checked to see what your default for data format is?

 

[isladogs]

Hi Elena

I've corrected several errors in the following code:

Private Sub cmdAdd_Click()

strUserName = Me.txtUserName
strPassword = Me.Password
intPasswordExpireDays = Nz(Me.txtExpireDays, 0)
blnChangePWD = IIf(Me.cboChangePWD = "Yes", -1, 0)
intAccessLevel = Nz(Me.cboLevel, 1)
If intPasswordExpireDays > 0 Then dtePwdDate = Date
'CHECK THE NEW PASSWORD TEXTBOX CONTENT HERE FOR VALIDITY

 'add new user
    If dtePwdDate <> 0 Then
        CurrentDb.Execute "INSERT INTO tblUsers ( UserName, Active, PWD, ChangePWD, ExpireDays, AccessLevel, PWDDate, Computer, initLogin, loginCount, nextPWdateChange)" & _
            " SELECT '" & strUserName & "' AS UserName, True AS Active, '" & RC4(strPassword, "RC4_Key") & "' AS PWD," & _
                " " & Me.cboChangePWD & " AS ChangePWD, " & intPasswordExpireDays & " AS ExpireDays," & _
                " " & intAccessLevel & " AS AccessLevel, #" & dtePwdDate & "# AS PWDDate, '" & GetComputerName & "' AS Computer, -1, 0, " & _
                "#" & DateAdd("d", Me.txtExpireDays, Date) & "#;"
    Else 'omit PWDDate
         CurrentDb.Execute "INSERT INTO tblUsers ( UserName, Active, PWD, ChangePWD, ExpireDays, AccessLevel, Computer, initLogin, loginCount, nextPWdateChange)" & _
            " SELECT '" & strUserName & "' AS UserName, True AS Active, '" & RC4(strPassword, "RC4_Key") & "' AS PWD," & _
                " " & Me.cboChangePWD & " AS ChangePWD, " & intPasswordExpireDays & " AS ExpireDays," & _
                " " & intAccessLevel & " AS AccessLevel, '" & GetComputerName & "' AS Computer, -1, 0," & _
                " #" & DateAdd("d", Me.txtExpireDays, Date) & "#;"
    End If

    Me.lblInfo.Caption = "New user " & Me.txtUserName & " has been successfully added" '& vbCrLf & _
        "A default password 'Not set' has been added" & vbCrLf & _
        Me.txtUserName & " will be required to enter a new password at first login"
'======================================================================
 
End Sub

Errors included:
1. Your Else section was identical to the If section. You hadn't omitted the PWDDate field
2. There was an extra ) at the end of the final line in both sections. The date itself wasn't causing the error. It was the extra )
3. You hadn't defined the ComputerName function. I've added a GetComputerName function to a new module modFunctions

Function GetComputerName()

    GetComputerName = Environ("ComputerName")
End Function

It now works for me. Hopefully it will also work for you

 

[lacampeona]

Hello
I still have problems. is maybe my database corrupted? shall i do all again in the new database?

in the module encryption... hmmmmm

Y = 0
Z = 0
ByteArray() = StrConv(Expression, vbFromUnicode)
For x = 0 To Len(Expression)
Y = (Y + 1) Mod 256
Z = (Z + rb(Y)) Mod 256
temp = rb(Y)
rb(Y) = rb(Z)
rb(Z) = temp
ByteArray(x) = ByteArray(x) Xor (rb((rb(Y) + rb(Z)) Mod 256))
Next x

thank you for all your help i Hope i will resolve that soon
Elena

 

[lacampeona]

isladogs,

I not know why you function is giving me error. sometimes it does and sometimes it does not. can I use what I find on internet? sha1 VBA function? I find it on stack overflow forum. your encryption code shows symbols in the table. the sha1 code gives letters and numbers. I am going try that. thank you.

 

[isladogs]

I wrote this earlier and forgot to click Send.

Not sure why you posted part of the RC4 encryption function.
The line in bold above is correct. It works!
Although I've used it for over 15 years, I didn't write it and please don't ask me to explain it.


UPDATE
Obviously you can use any code you wish.
I'm not familiar with the SHA1 code you've found at SO but its likely to be a weaker form of encryption if it just uses alphanumeric characters.
The RC4 encryption code uses the full range of the ASCII characters including 'symbols' and provides strong encryption which is virtually impossible to crack unless the RC4 cipher is known,

You said it doesn't always work for you but didn't give any details about what issues you are having.
Without more information, I can't help you with that

 

[lacampeona]

Isladogs,
I will try to use sha1 code because not sure what is wrong with yours. i will respond if it works. i might have more issue with date code too.
i am batteling here to see what will happen in the final. the biggest problem is something that was in RC4 and the dates.
thank you very much
Elena

 

[isladogs]

As I said its not my code, but its been very well tested by numerous people including my clients over many years.
I can assure you there is nothing wrong with that code.

 

[lacampeona]

Yes I believe you.
it was working also for me very fine and then i make some new forms and i get problems with the dates and RC4.
I will see what will happen in the final.
thank you
Elena