Whats wrong in this code

[Ashfaque]

Hi Gents,

I have below code thru which I am transfering records to Excel sheet from T_SOHeader table and T_SOFooter1 table which are based on One-To-Many relationship. I have many records existing in said header and footer table. I need to pick them up with date criteria for a particular period.

My code to open Excel application etc are fine.

Here is the vba code that says No Records

----codes to open Excel...
----
-----
Set rst = CurrentDb.OpenRecordset("SELECT T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName, " & _
"T_SOFooter1.ProductName, T_SOFooter1.ProductClass, T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty as PromoQty, " & _
"WeightInKgPerBag, (NoOfBags + FreeQty) As TotQty, ((NoOfBags + FreeQty) * WeightInKgPerBag /1000) As " & _
"MT, T_SOFooter1.RecordId " & _
"FROM T_SOHeader LEFT JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum " & _
"WHERE (T_SOHeader.SODate) >= " & #1/12/2009# & " And " & _
"(T_SOHeader.SODate) <= " & #12/25/2009# & " " & _
"Group By T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName,T_SOFooter1.ProductName, " & _
"T_SOFooter1.ProductClass,T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty, T_SOFooter1.WeightInKgPerBag, " & _
"T_SOFooter1.RecordId;")

If rst.EOF And rst.BOF Then
MsgBox ("No Records In This Month"), vbInformation, "Null Records Inf."
Exit Sub
Else
----other code lines...

I am entering dates thru 2 text boxes palced on a form called F_ReportMaster. So tried even to change the criteria below way BUT IT DESPLAYS my given msg "No Records In This Month"...

'"WHERE (T_SOHeader.SODate) >= " & [Forms]![F_ReportMaster]![TxtFrom] & " And " & _
'"(T_SOHeader.SODate) <= " & [Forms]![F_ReportMaster]![TxtTo] & " " & _

Can you please extend your help.....

With kind regards,

 

[philosofe]

Hi there,

I've noticed that, using European date formatting (dd/mm/yyyy), without extra formatting Dec. 1st becomes Jan 12th when passing the value to SQL - so try enclosing the dates withing a Format(<Field>, "mm/dd/yyyy") and it should work. Not beautiful, but functional.

Of course, this could mess things up when dealing with computers with different locale, e.g. U.S.

 

[Ashfaque]

Thanks,

My dates are stored in "dd/mm/yyyy" format always. When I remove complete 'Where' clause from the code and runs, it gives me ALL records with date with (dd/mm/yyyy format), customername, productname etc fields in desired Excel sheet format. It means there is no problem with the date format.

There must be some other thing gone wrong...whats that need to know....

Regards,

 

[stopher]

The code you have given suggests you are looking for records in the date range
12th Jan 2009 - 25th Dec 2009. Are you saying you definitely have records in that range?

What happens when you use dates 1/1/2007 - 12/31/2011 ?

For what periods do you have dates for?

Chris

 

[Ashfaque]

Attached is the error when I used your date range. And there is minor change below in criteria as well I did to suit situatioin.

Set rst = CurrentDb.OpenRecordset("SELECT T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName, " & _
"T_SOFooter1.InvNum, T_SOFooter1.ProductName, T_SOFooter1.ProductClass, T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty as PromoQty, " & _
"WeightInKgPerBag, (NoOfBags + FreeQty) As TotQty, ((NoOfBags + FreeQty) * WeightInKgPerBag /1000) As MT, T_SOFooter1.RecordID " & _
"WHERE T_SOHeader.SODate >= " & #1/1/2007# & " And " & _
"T_SOHeader.SODate >= " & #12/31/2011# & " " & _
"FROM T_SOHeader LEFT JOIN T_SOFooter1 ON T_SOHeader.InvNum = T_SOFooter1.InvNum " & _
"Group By T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName,T_SOFooter1.ProductName, " & _
"T_SOFooter1.ProductClass,T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty, T_SOFooter1.WeightInKgPerBag, " & _
"T_SOFooter1.RecordID Order By T_SOFooter1.InvNum;")

Regards,

 

[Ashfaque]

I have below date range in actual table
Records from 01/08/2009 to 29/12/2009.

Regards,

 

[stopher]

Hi Gents,

I have below code thru which I am transfering records to Excel sheet from T_SOHeader table and T_SOFooter1 table which are based on One-To-Many relationship. I have many records existing in said header and footer table. I need to pick them up with date criteria for a particular period.

My code to open Excel application etc are fine.

Here is the vba code that says No Records

----codes to open Excel...
----
-----
Set rst = CurrentDb.OpenRecordset("SELECT T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName, " & _
"T_SOFooter1.ProductName, T_SOFooter1.ProductClass, T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty as PromoQty, " & _
"WeightInKgPerBag, (NoOfBags + FreeQty) As TotQty, ((NoOfBags + FreeQty) * WeightInKgPerBag /1000) As " & _
"MT, T_SOFooter1.RecordId " & _
"FROM T_SOHeader LEFT JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum " & _
"WHERE (T_SOHeader.SODate) >= " & #1/12/2009# & " And "# & _
"(T_SOHeader.SODate) <= " & #12/25/2009# & " " & _
"Group By T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName,T_SOFooter1.ProductName, " & _
"T_SOFooter1.ProductClass,T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty, T_SOFooter1.WeightInKgPerBag, " & _
"T_SOFooter1.RecordId;")

If rst.EOF And rst.BOF Then
MsgBox ("No Records In This Month"), vbInformation, "Null Records Inf."
Exit Sub
Else
----other code lines...

I am entering dates thru 2 text boxes palced on a form called F_ReportMaster. So tried even to change the criteria below way BUT IT DESPLAYS my given msg "No Records In This Month"...

'"WHERE (T_SOHeader.SODate) >= " & [Forms]![F_ReportMaster]![TxtFrom] & " And " & _
'"(T_SOHeader.SODate) <= " & [Forms]![F_ReportMaster]![TxtTo] & " " & _

Can you please extend your help.....

With kind regards,

I've just run your query and the problem is where you have the #'s. Your code should be:

Set rst = CurrentDb.OpenRecordset("SELECT T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName, " & _
"T_SOFooter1.ProductName, T_SOFooter1.ProductClass, T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty as PromoQty, " & _
"WeightInKgPerBag, (NoOfBags + FreeQty) As TotQty, ((NoOfBags + FreeQty) * WeightInKgPerBag /1000) As " & _
"MT, T_SOFooter1.RecordId " & _
"FROM T_SOHeader LEFT JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum " & _
"WHERE (T_SOHeader.SODate) >= #" & "1/12/2009" & " And "# & _
"(T_SOHeader.SODate) <= #" & "12/25/2009" & "# " & _
"Group By T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName,T_SOFooter1.ProductName, " & _
"T_SOFooter1.ProductClass,T_SOFooter1.NoOfBags , T_SOFooter1.FreeQty, T_SOFooter1.WeightInKgPerBag, " & _
"T_SOFooter1.RecordId;")

Chris

 

[Ashfaque]

Thanks,

There is syntax errror in the code....where it is I could not trace it out ..I think in criteria....

Regards,

 

[stopher]

Sorry, there was a hash still in the wrong place. Try this:

Set rst = CurrentDb.OpenRecordset("SELECT T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName, " & _
"T_SOFooter1.ProductName, T_SOFooter1.ProductClass, T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty as PromoQty, " & _
"WeightInKgPerBag, (NoOfBags + FreeQty) As TotQty, ((NoOfBags + FreeQty) * WeightInKgPerBag /1000) As " & _
"MT, T_SOFooter1.RecordId " & _
"FROM T_SOHeader LEFT JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum " & _
"WHERE (T_SOHeader.SODate) >= #" & "1/12/2009" & "# And " & _
"(T_SOHeader.SODate) <= #" & "12/25/2009" & "# " & _
"Group By T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName,T_SOFooter1.ProductName, " & _
"T_SOFooter1.ProductClass,T_SOFooter1.NoOfBags , T_SOFooter1.FreeQty, T_SOFooter1.WeightInKgPerBag, " & _
"T_SOFooter1.RecordId;")

 

[Ashfaque]

Thanks,

Although there is no compilation error this time and it gives me data to BUT it did not accepted criteria and displayed all records from start to end....

I toggled the code and tried to see but doesnt show anything while our date format and range in right way.

I have attached result that displayed in Excel sheet for your ref.

Please extend your help.

Regards,
Ashfaque

 

[Ashfaque]

Below code is working but I think there is only problem remain of data format. My dates are stored in access tbl like dd/mm/yyyy but below code reads it mm/dd/yyyy and hence it is displayed mixed data.

Set rst = CurrentDb.OpenRecordset("SELECT T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName, " & _
"T_SOFooter1.ProductName, T_SOFooter1.ProductClass, T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty as PromoQty, " & _
"WeightInKgPerBag, (NoOfBags + FreeQty) As TotQty, ((NoOfBags + FreeQty) * WeightInKgPerBag /1000) As " & _
"MT, T_SOFooter1.RecordId " & _
"FROM T_SOHeader INNER JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum " & _
"WHERE T_SOHeader.SODate >= " & "#11/22/2009#" & " And (T_SOHeader.SODate) <= " & "#12/15/2009#" & " " & _
"Group By T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName,T_SOFooter1.ProductName, " & _
"T_SOFooter1.ProductClass,T_SOFooter1.NoOfBags , T_SOFooter1.FreeQty, T_SOFooter1.WeightInKgPerBag, " & _
"T_SOFooter1.RecordId;")

How can I set format dd/mm/yyyy in above code?

Regards,
Ashfaque

 

[stopher]

Sounds like we're getting somewhere. We're now on the problem/solution raised by Philosofe. Change your where line to:

"WHERE T_SOHeader.SODate >= " & "#" & FORMAT("11/22/2009","mm/dd/yyyy") & "#" & " And (T_SOHeader.SODate) <= " & "#" & FORMAT("12/15/2009","mm/dd/yyyy") & "#" & " " & _

hth
Chris

 

[Ashfaque]

Thanks Chris,

I used your format criteria with changed dates as below

"WHERE T_SOHeader.SODate >= " & "#" & Format("12/01/2009", "mm/dd/yyyy") & "#" & " And (T_SOHeader.SODate) <= " & "#" & Format("12/31/2009", "mm/dd/yyyy") & "#" & " " & _

This gives me no error BUT with mess up date range records that I attached in pic.

I will retype the dates manually in dd/mm/yyyy format for 30-40 records and will see then the result. If it does smoothly then it means there had problem in storing date field data in my data.

If the problme still persist, we need another solution to get desired result.

I will come back to you again.

Thanks.
Ashfaque

 

[Ashfaque]

Hi,

I don't understand why it produces the mess up dates.

I have all retyped dates for month of August 2009 in my table in dd/mm/yyyy format. They are about 40 records.

Then provided criterial date range thur a form that is in between range I retyped in the table and result is in the attached.

"WHERE T_SOHeader.SODate >= " & "#" & Format(Forms!F_ReportMaster!TxtFrom, "mm/dd/yyyy") & "#" & " And " & _
"(T_SOHeader.SODate) <= " & "#" & Format(Forms!F_ReportMaster!TxtTo, "mm/dd/yyyy") & "#" & " " & _

No compilation error and displaying records but why it is assuming dd/mm/yyyy format for some records and mm/dd/yyyy for some records.

I believe some one definately would help me.....

Regards,
Ashfaque

 

[Poppa Smurf]

If you post a 2003 version of the database to the forum I will try and help you.

 

[Ashfaque]

Thanks,

Here is the database and its Excel File I attached for you.

Please change the path in vba module.

Also please select date range as 01/08/09 to 25/08/09 and click Monthy Sales button on form.

Set Excel ref as required.

Thanks again.

Regards
Ashfaque

 

[stopher]

Hi Ashfaque

There is a line in your code that looks like this:

objSht.Cells(iRow, 2).Value = Format(rst!SODate, "Short date")

It needs to be changed to look like this (otherwise the date will be interpreted as US where possible):

objSht.Cells(iRow, 2).Value = Format(Format(rst!SODate, "mm,dd,yy"), "Short date")

hth
Chris

 

[Ashfaque]

Excellent observation....Bravo...

Thanks.....

 

[Ashfaque]

Hi,

One small question, is it possible to display one time customer name and its relevant date with one or more products in the Excel list?

The current date is displayed in below way.

1. 01/12/2009 Trass International Tile Adhesvies White ... ....
2. 01/12/2009 Trass International Tile Adhesvies Gray ... ....
3. 01/12/2009 Trass International Tile Grout Gray ... ....
4. 03/12/2009 City Light Trading Construction Grout ... ....
5. 03/12/2009 City Light Trading GP Putty ... ....
So on..

I need this way.

1. 01/12/2009 Trass International ............Tile Adhesvies White ... ....
.........................................................Tile Adhesvies Gray ... ....
.........................................................Tile Grout Gray ... ....
2. 03/12/2009 City Light Trading ..............Construction Grout ... ....
.........................................................GP Putty ... ....
So on..

I mean sr no (optional), Date and Customer code should not appear more than once.

If it requires much efforts to re-arrange vba code then please leave it

If not then please extend your help.

With kind regards,
Ashfaque

 

[Poppa Smurf]

Ashfaque

In the attached file I have changed your code for the section of the module used to select the date. I have use the Between format in lieu of your >= and <=.
Here is a copy of the code. I will attempt to explain the changes.

The DAO highlighted was added

I broke your original SELECT statement into a series of strings, this makes it easier to read and debug. I then joined the strings in the line of code Set RST=

Note. The attached file also includes the code change suggested by stopher and it works.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sqlstring_1 As String
Dim sqlstring_2 As String
Dim sqlstring_3 As String
Dim sqlstring_4 As String
Dim sqlstring_5 As String
Dim sqlstring_6 As String
Dim sqlstring_7 As String
sqlstring_1 = "SELECT T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName, T_SOFooter1.ProductName, " & _
"T_SOFooter1.ProductClass, T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty AS PromoQty, T_SOFooter1.WeightInKgPerBag, (NoOfBags+FreeQty) AS TotQty, " & _
"((NoOfBags+FreeQty)*WeightInKgPerBag/1000) AS MT, T_SOFooter1.RecordID " & _
"FROM T_SOHeader LEFT JOIN T_SOFooter1 ON T_SOHeader.InvNum = T_SOFooter1.InvNum "
sqlstring_2 = "WHERE (((T_SOHeader.SODate) Between # "
sqlstring_3 = Format(Forms!f_reportmaster!TxtFrom, "mm/dd/yyyy")
sqlstring_4 = "# and # "
sqlstring_5 = Format(Forms!f_reportmaster!TxtTo, "mm/dd/yyyy")
sqlstring_6 = " # )) "
sqlstring_7 = "GROUP BY T_SOHeader.InvNum, T_SOHeader.SODate, T_SOHeader.CustomerName, T_SOFooter1.ProductName, T_SOFooter1.ProductClass, " & _
"T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty, T_SOFooter1.WeightInKgPerBag, T_SOFooter1.RecordId " & _
"ORDER BY T_SOHeader.SODate"
Set db = CurrentDb()
Set rst = db.OpenRecordset(sqlstring_1 & sqlstring_2 & sqlstring_3 & sqlstring_4 & sqlstring_5 & sqlstring_6 & sqlstring_7)

' the rest of your code goes here
If rst.EOF And rst.BOF Then
MsgBox ("No Records In This Month"), vbInformation, "Null Records Inf."
Exit Sub
Else