How it Work function Rnd()?

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.
 
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.
 
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?
 
Last edited:
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.
Click to expand...

Is it at all possible to avoid giving this error for the dates? And why two export buttons to the documents do not work?
 
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?
Click to expand...

Try changing the code as I have indicated. It worked for me.?
 
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
 
Gasman said:
Code: 
? "#" & Format(thedate, "mm/dd/yyyy") & "#"
#04.25.2019#

Code: 
? "#" & Replace(Format(thedate,"mm/dd/yyyy"),".","/") & "#"
Click to expand...

Which of the two codes is the correct one and which three places will I replace it to work with the buttons?
 
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.
 
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.
Click to expand...

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.
 

Attachments

  • Error.jpg
    Error.jpg
    87.1 KB · Views: 147
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.
 
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.
Click to expand...

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
 
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.:confused:
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.
 
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.:confused:
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.
Click to expand...

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 ...
 
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.:confused: 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.?
 
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
 
@Gasman
However if I change to estonian settings, it does not obey the format?
Click to expand...
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.
 
@ MajP
I am just intrigued as to why it does not obey the Format string layout.?
 
Kris86BsBG,

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

Успех с вашия проект
 
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
Click to expand...

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?
 

Attachments

  • WordPrint.jpg
    WordPrint.jpg
    92.9 KB · Views: 135
You must log in or register to reply here.

Similar threads

nicolareina
Single row data query
Replies
13
Views
377
Gasman
Gasman
L
How to get results in lookup field from another lookup field?
Replies
6
Views
461
mike60smart
mike60smart
essaytee
[SOLVED] Calculated column not sorting by largest to smallest
Replies
1
Views
208
essaytee
essaytee
D
Table Search and Total
Replies
18
Views
627
DDJKSA
D
J
Open PDF files in Nitro instead of Adobe reader
Replies
13
Views
670
Isaac
Isaac

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom