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

lacampeona

Registered User.
Local time
Today, 09:01
Joined
Dec 28, 2015
Messages
392
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:
Code:
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:
Code:
    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

I’m here to help
Staff member
Local time
Today, 01:01
Joined
Oct 29, 2018
Messages
21,322
Hi. Try using Format(DateVariable, "yyyy-mm-dd").
 

plog

Banishment Pending
Local time
Today, 03:01
Joined
May 11, 2011
Messages
11,597
I think its your sql, not your formatting. Here's a link to an INSERT query:


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

Registered User.
Local time
Today, 09:01
Joined
Dec 28, 2015
Messages
392
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.
 

Attachments

  • error 0407 002 .JPG
    error 0407 002 .JPG
    209.3 KB · Views: 578
  • error 0407.JPG
    error 0407.JPG
    22.4 KB · Views: 586

namliam

The Mailman - AWF VIP
Local time
Today, 09:01
Joined
Aug 11, 2003
Messages
11,696
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

MVP / VIP
Local time
Today, 08:01
Joined
Jan 14, 2017
Messages
18,164
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

Code:
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

Registered User.
Local time
Today, 09:01
Joined
Dec 28, 2015
Messages
392
Hello
I still get the error

hmm
what i am doing wrong?
 

Attachments

  • error 0407 003 .JPG
    error 0407 003 .JPG
    26.3 KB · Views: 550

isladogs

MVP / VIP
Local time
Today, 08:01
Joined
Jan 14, 2017
Messages
18,164
What was the code that you used this time?

EDIT
Try commenting out the If and End If lines
 

lacampeona

Registered User.
Local time
Today, 09:01
Joined
Dec 28, 2015
Messages
392
hi
i used your code that you send.
ok i will try
thanks
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:01
Joined
Feb 19, 2013
Messages
16,521
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

Registered User.
Local time
Today, 09:01
Joined
Dec 28, 2015
Messages
392
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

Super Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 19, 2002
Messages
42,872
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

MVP / VIP
Local time
Today, 08:01
Joined
Jan 14, 2017
Messages
18,164
Hi Elena

I've corrected several errors in the following code:

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

Code:
Function GetComputerName()

    GetComputerName = Environ("ComputerName")
End Function

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

Attachments

  • for_forum_help_CR.zip
    113.6 KB · Views: 273

lacampeona

Registered User.
Local time
Today, 09:01
Joined
Dec 28, 2015
Messages
392
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

Registered User.
Local time
Today, 09:01
Joined
Dec 28, 2015
Messages
392
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

MVP / VIP
Local time
Today, 08:01
Joined
Jan 14, 2017
Messages
18,164
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. :oops:


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

Registered User.
Local time
Today, 09:01
Joined
Dec 28, 2015
Messages
392
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

MVP / VIP
Local time
Today, 08:01
Joined
Jan 14, 2017
Messages
18,164
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

Registered User.
Local time
Today, 09:01
Joined
Dec 28, 2015
Messages
392
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
 

Users who are viewing this thread

Top Bottom