How it Work function Rnd()? (1 Viewer)

MajP · Apr 25, 2019

I know what is happening, but not sure I understand it. This has to do with regional settings.
In the US dates are MM/dd/YYYY in Europe dates are often DD/MM/YYYY.
SQL can only process dates as MM/DD/YYYY
Also we do decimals like $1.00 and europe does 1,00

So there is an issue between my regional settings and yours. I am not sure how you got
4.25.219 instead of 4/25/2019. I need to think about that.

MajP · Apr 25, 2019

I purposely wrote the code

Code:

DoCmd.OpenReport "rptHoroscope", acViewPreview, , "HoroscopeDate = #" & Format(Me.txtDate, "MM/DD/YYYY") & "#"

To avoid regional settings, but somewhere I failed.

Gasman · Apr 25, 2019

Well it works for my UK settings.

However if I change to estonian settings, it does not obey the format?

Code:

? "#" & Format(thedate, "mm/dd/yyyy") & "#"
#04.25.2019#

Code:

? "#" & Replace(Format(thedate,"mm/dd/yyyy"),".","/") & "#"

works, but clunky?

KrIs86BsBG · Apr 26, 2019

MajP said:
I purposely wrote the code

Code:

DoCmd.OpenReport "rptHoroscope", acViewPreview, , "HoroscopeDate = #" & Format(Me.txtDate, "MM/DD/YYYY") & "#"

To avoid regional settings, but somewhere I failed.

Is it at all possible to avoid giving this error for the dates? And why two export buttons to the documents do not work?

Gasman · Apr 26, 2019

KrIs86BsBG said:
Is it at all possible to avoid giving this error for the dates? And why two export buttons to the documents do not work?

Try changing the code as I have indicated. It worked for me.?

KrIs86BsBG · Apr 26, 2019

Gasman said:
Code:

? "#" & Format(thedate, "mm/dd/yyyy") & "#" #04.25.2019#

Code:

? "#" & Replace(Format(thedate,"mm/dd/yyyy"),".","/") & "#"

Which one and where exactly will I change it?

Gasman · Apr 26, 2019

The format code is in three places in the form.
One has thedate as shown, the other two have txtdate

thedate is in function HasDate
txtdate is in each button's click event

HTH

KrIs86BsBG · Apr 26, 2019

Gasman said:
Code:

? "#" & Format(thedate, "mm/dd/yyyy") & "#" #04.25.2019#

Code:

? "#" & Replace(Format(thedate,"mm/dd/yyyy"),".","/") & "#"

Which of the two codes is the correct one and which three places will I replace it to work with the buttons?

Gasman · Apr 26, 2019

Well we know the first one does not work for your regional settings, so it will be the second one.?

Just use the correct variable in each place.
Each button uses it once as does the function.

KrIs86BsBG · Apr 26, 2019

MajP said:
I purposely wrote the code

Code:

DoCmd.OpenReport "rptHoroscope", acViewPreview, , "HoroscopeDate = #" & Format(Me.txtDate, "MM/DD/YYYY") & "#"

To avoid regional settings, but somewhere I failed.

This also shows me the error, so the export buttons do not work in a Word document. See the picture.

I tried to change the codes but gave me a mistake. Can anyone help with the correct code to work. Thanks in advance.

Gasman · Apr 27, 2019

You haven't changed the code with my amendments.? That is MajP's original code.?
That is one of the places you need to use my code until MajP comes up with a better solution. The other is for the word button and then a similar line in the HasDate function.

KrIs86BsBG · Apr 27, 2019

Gasman said:
You haven't changed the code with my amendments.? That is MajP's original code.?
That is one of the places you need to use my code until MajP comes up with a better solution. The other is for the word button and then a similar line in the HasDate function.

Can you change the base code to work for export to the documents and not give this error, because I do not know what to change and send me here if you can? Download the latest attachment: Horoscopes_V2

Gasman · Apr 27, 2019

No, you need to make the changes yourself. You are not going to learn anything if people do all the work for you.
MajP has done a huge amount of work for you already and you have not even thanked him.

All you need to do is copy and paste and perhaps a little editing where I have indicated.

Believe me, amending the code is a better learning experience than just reading it.

KrIs86BsBG · Apr 27, 2019

Gasman said:
No, you need to make the changes yourself. You are not going to learn anything if people do all the work for you.
MajP has done a huge amount of work for you already and you have not even thanked him.
All you need to do is copy and paste and perhaps a little editing where I have indicated.

Believe me, amending the code is a better learning experience than just reading it.

I am very grateful. I personally thanked him for everything, the question is to tell me what to change because I do not understand you ...

This is the Code that does not work:

Code:

HasDate = (DCount("*", "tblHoroscope", "HoroscopeDate = #" & Format(theDate, "MM/DD/YYYY") & "#") > 0)

And this is for the buttons:

Code:

DoCmd.OpenReport "rptHoroscope", acViewPreview, , "HoroscopeDate = #" & Format(Me.txtDate, "DD/MM/YYYY") & "#"
  DoCmd.OutputTo acOutputReport, "RptHoroscope", acFormatRTF

What exactly should I change? As you described it, I do not understand ...

Gasman · Apr 27, 2019

Ok,

For some reason the Format function is not working as expected.
It is meant to make the date in the format mm/dd/yyyy so today would be 04/27/2019
Due to your regional settings your dates are still coming out as 04.27.2019 which Access does not like.
So my workaround was to replace the . with the / so that Access will then accept the date.
I've changed it in my downloaded DB and can confirm that now works.
YOU need to do the same.

Seriously, if this is beyond you, you might want to consider another hobby as this is just basic editing code.

You will not be able to progress further with this project in any way (unless someone again does all the work for you).
I've given you what you need, but I will do it one more time.

Replace the Format code in the places I have specified with the Replace code below. Change the yourdatevariable to the correct variable, which will be the one in the code now.

Code:

Replace(Format(yourdatevariable,"mm/dd/yyyy"),".","/")

I know I could just upload my corrected DB, but again you will learn nothing. That is not really helping you in my opinion.?

MajP · Apr 27, 2019

Add this function to the module mdlRandom

Code:

Public Function BulgarianDate(TheDate As Date) As String
  'Converts a date with bulgarian settings to use in SQL
  BulgarianDate = "#" & Month(TheDate) & "/" & Day(TheDate) & "/" & Year(TheDate) & "#"
End Function

Change code in form in these places.

Code:

Private Sub cmdPDF_Click()
  DoCmd.OpenReport "rptHoroscope", acViewPreview, , "HoroscopeDate = " & BulgarianDate(Me.txtDate)
  DoCmd.OutputTo acOutputReport, "RptHoroscope", acFormatPDF
End Sub
Private Sub cmdWord_Click()
  DoCmd.OpenReport "rptHoroscope", acViewPreview, , "HoroscopeDate = " & BulgarianDate(Me.txtDate)
  DoCmd.OutputTo acOutputReport, "RptHoroscope", acFormatRTF
End Sub

Change the code in the module mdlRandom

Code:

Public Function HasDate(TheDate As Date) As Boolean
  HasDate = (DCount("*", "tblHoroscope", "HoroscopeDate = " & BulgarianDate(TheDate)) > 0)
End Function

Code:

Public Sub CreateHoroscope2(TheDate As Date)
  'Creates 30 days out of horoscope without repitition
  
  Dim strSql As String
  Dim rszodiac As DAO.Recordset
  Dim rsLove As DAO.Recordset
  Dim rsWork As DAO.Recordset
  Dim rsFinance As DAO.Recordset
  Dim i As Integer
  Dim LoveID As Long
  Dim WorkID As Long
  Dim ZodiacID As Long
  Dim FinanceID As Long
  Dim HoroscopeDate As String
  Dim NewDate As Date
  Dim maxMessages As Integer
  NewDate = TheDate
  
  Set rszodiac = CurrentDb.OpenRecordset("Select ZodiacID from Zodiac")
  maxMessages = DCount("*", "Love")
  If DCount("*", "Finance") < maxMessages Then maxMessages = DCount("*", "Finance")
  If DCount("*", "Work") < maxMessages Then maxMessages = DCount("*", "Work")
  Do While Not rszodiac.EOF ' do each zodiac
     Set rsWork = CurrentDb.OpenRecordset("qryRandomWOrk")
     Set rsLove = CurrentDb.OpenRecordset("qryRandomLove")
     Set rsFinance = CurrentDb.OpenRecordset("qryRandomFinance")
     ZodiacID = rszodiac!ZodiacID
    For i = 1 To maxMessages ' each available message
        HoroscopeDate = [B]BulgarianDate(TheDate)[/B]
        LoveID = rsLove!LoveID
        WorkID = rsWork!WorkID
        FinanceID = rsFinance!FinanceID
        strSql = "Insert Into tblHoroscope (HoroscopeDate, ZodiacID_FK, LoveID_FK, WorkID_FK, FinanceID_FK) "
        strSql = strSql & "values (" & HoroscopeDate & ", " & ZodiacID & ", " & LoveID & ", " & WorkID & ", " & FinanceID & ")"
    Debug.Print strSql
        rsWork.MoveNext
        rsLove.MoveNext
        rsFinance.MoveNext
        CurrentDb.Execute strSql
        NewDate = NewDate + 1
    Next i
    NewDate = TheDate
    rszodiac.MoveNext
  Loop
End Sub

MajP · Apr 27, 2019

@Gasman

However if I change to estonian settings, it does not obey the format?

Thanks for identifying that. I doubt I would have ever figured that one out. I have seen strange regional setting issues, but never seen that before.

Gasman · Apr 27, 2019

@ MajP
I am just intrigued as to why it does not obey the Format string layout.?

jdraw · Apr 27, 2019

Kris86BsBG,

Английският не е вашият роден език и тъй като читателите изпитват затруднения да разберат вашето изискване и описание, препоръчвам ви
напишете описанието си и всички въпроси на родния си език, след което използвайте Google за превод на родния ви език на английски.
Колкото по-бързи читатели могат да разберат нашето изискване, толкова по-скоро ще получите съсредоточен съвет.

Успех с вашия проект

KrIs86BsBG · Apr 28, 2019

MajP said:

Add this function to the module mdlRandom

Code:

Public Function BulgarianDate(TheDate As Date) As String
  'Converts a date with bulgarian settings to use in SQL
  BulgarianDate = "#" & Month(TheDate) & "/" & Day(TheDate) & "/" & Year(TheDate) & "#"
End Function

Change code in form in these places.

Code:

Private Sub cmdPDF_Click()
  DoCmd.OpenReport "rptHoroscope", acViewPreview, , "HoroscopeDate = " & BulgarianDate(Me.txtDate)
  DoCmd.OutputTo acOutputReport, "RptHoroscope", acFormatPDF
End Sub
Private Sub cmdWord_Click()
  DoCmd.OpenReport "rptHoroscope", acViewPreview, , "HoroscopeDate = " & BulgarianDate(Me.txtDate)
  DoCmd.OutputTo acOutputReport, "RptHoroscope", acFormatRTF
End Sub

Change the code in the module mdlRandom

Code:

Public Function HasDate(TheDate As Date) As Boolean
  HasDate = (DCount("*", "tblHoroscope", "HoroscopeDate = " & BulgarianDate(TheDate)) > 0)
End Function

Code:

Public Sub CreateHoroscope2(TheDate As Date)
  'Creates 30 days out of horoscope without repitition
  
  Dim strSql As String
  Dim rszodiac As DAO.Recordset
  Dim rsLove As DAO.Recordset
  Dim rsWork As DAO.Recordset
  Dim rsFinance As DAO.Recordset
  Dim i As Integer
  Dim LoveID As Long
  Dim WorkID As Long
  Dim ZodiacID As Long
  Dim FinanceID As Long
  Dim HoroscopeDate As String
  Dim NewDate As Date
  Dim maxMessages As Integer
  NewDate = TheDate
  
  Set rszodiac = CurrentDb.OpenRecordset("Select ZodiacID from Zodiac")
  maxMessages = DCount("*", "Love")
  If DCount("*", "Finance") < maxMessages Then maxMessages = DCount("*", "Finance")
  If DCount("*", "Work") < maxMessages Then maxMessages = DCount("*", "Work")
  Do While Not rszodiac.EOF ' do each zodiac
     Set rsWork = CurrentDb.OpenRecordset("qryRandomWOrk")
     Set rsLove = CurrentDb.OpenRecordset("qryRandomLove")
     Set rsFinance = CurrentDb.OpenRecordset("qryRandomFinance")
     ZodiacID = rszodiac!ZodiacID
    For i = 1 To maxMessages ' each available message
        HoroscopeDate = [B]BulgarianDate(TheDate)[/B]
        LoveID = rsLove!LoveID
        WorkID = rsWork!WorkID
        FinanceID = rsFinance!FinanceID
        strSql = "Insert Into tblHoroscope (HoroscopeDate, ZodiacID_FK, LoveID_FK, WorkID_FK, FinanceID_FK) "
        strSql = strSql & "values (" & HoroscopeDate & ", " & ZodiacID & ", " & LoveID & ", " & WorkID & ", " & FinanceID & ")"
    Debug.Print strSql
        rsWork.MoveNext
        rsLove.MoveNext
        rsFinance.MoveNext
        CurrentDb.Execute strSql
        NewDate = NewDate + 1
    Next i
    NewDate = TheDate
    rszodiac.MoveNext
  Loop
End Sub

1. Thanks again for the work done! I changed the codes to the places you told me and the buttons are already working. However, there is a discrepancy between what appears in the form and the printed version. Shifts the signs and does not arrange them as they are one after the other ... Can this be fine? I attached a picture.

2. Will this entire shuffle work for every date on the calendar as you did it from 21.04.2019 to 12.11.2019. That's 205 days. I add 205 text for each category. Will it mix them everyday to be a different text without repetition for all the days?

How it Work function Rnd()? (1 Viewer)

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Attachments

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Enthusiastic Amateur

Super Moderator

Registered User.

Attachments

Similar threads

Users who are viewing this thread