Problem to Filter query

Ashk49 · Jun 26, 2008

Hi, first, thanks in advance.

Let me try to explain my problem:I made a great query in Microsoft Excel that Imported from SQl. Several fields ,Definitely database is huge and I cant import all data to Excel (Max 65K records). This is what I need ! I made Text box to able to get the DATE entry from customer and my query filter that day and imported to Excel. Data format in the SQL that I have no access to that is like :yyyy-mm-dd h:mm:ss and the Text Box it will be just the DATE :mm-dd/yyyy
.To this point I wrote the code and fix it but when I try to use my parameter in the query instead of Fix Date-Time ,SQL syntax error will pup. my code that involve to this issue is following:

Rich (BB code):

Dim Filter_Date
Dim Begin_Of_Day 
Dim End_Of_Day  
 
    Filter_Date = Application.Sheets("ATL").TextBox1.Value
  ' test
  ' Range("P11").Select
  ' ActiveCell.Value = Filter_Date
  ' Selection.Copy
   Sheets("DATA2").Select
   Range("J2").Select
   Selection.NumberFormat = "yyyy-mm-dd h:mm:ss.000"
   ActiveCell.Value = Filter_Date
   Selection.NumberFormat = "General"
    Selection.Copy
    Range("J3:J4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
      Selection.NumberFormat = "General"
'made Date in general format ,ready to add Time to the number and reconvert it
   '-------------start time -----------------
    Range("J3").Select
    Begin_Of_Day = ActiveCell.Value + 0.00001
    Range("J3").Value = Begin_Of_Day
    Selection.NumberFormat = "yyyy-mm-dd h:mm:ss.000"
    Begin_Of_Day = Range("J3").Value
          Range("J5").Value = Begin_Of_Day
   '--------------End time -------------------
    Range("J4").Select
    End_Of_Day = ActiveCell.Value + 0.99999
    Range("J4").Value = End_Of_Day
    Selection.NumberFormat = "yyyy-mm-dd h:mm:ss.000"
    End_Of_Day = Range("J4").Value.........
...........
...............
.................
...................
....................
  .CommandText = Array( _
        "SELECT ProductLine.LineID, LineRun.RunID, Product.ProductID, Material.MaterialName, ProductLine.UnitsPerMin, RunLineDeviceRecipe.UnitWt_SET, LineRun.ActualUnits, LineRun.ActualWt, LineRun.DateOpened, LineRun.DateClosed," _
        , _
        "ProductLine.ProductID, LineDeviceStateLog.DTReasonEX, Product.ProductID" & Chr(13) & "" & Chr(10) & "FROM  LineRun LineRun, Material Material, Product Product,Pr" _
        , _
        "oductLine ProductLine, RunLineDeviceRecipe RunLineDeviceRecipe ,LineDeviceStateLog LineDeviceStateLog " & Chr(13) & "" & Chr(10) & "WHERE Material.Material" _
        , _
        "ID = Product.MaterialID AND ProductLine.ProductID = Product.ProductID AND RunLineDeviceRecipe.LineID = ProductLine.LineID AND RunLineDeviceRecipe.RunID = LineRun.RunID AND  ProductLine.LineID = LineRun.LineID AND LineDeviceS" _
        , _
        "tateLog.LineID = LineRun.LineID AND LineDeviceStateLog.DateClosed = LineRun.DateClosed AND LineDeviceStateLog.DateOpened = LineRun.DateOpened AND" _
        , _
        "((LineRun.DateOpened>={ts (Begin_Of_Day) } And LineRun.DateOpened<={ts ( End_Of_Day)  }))" & Chr(13) & "" & Chr(10) & "ORDER BY ProductL" _
        , _
        "ine.LineID, Product.ProductID, LineRun.RunID" _
        )

Filter codes marked blue.
Did I made myself clear? Can you help me with this problem?

Thanks a lot!!!
Ashkan

Fazza · Jul 2, 2008

Hi Ashkan.

A quick review of the posted code shows that you are formating the worksheet cells to the format you want. However this is not really helping achieve what you want because the cell format is only its appearance. The stored number does not change and this won't achieve what you want.

Does that make sense. What is happening is like this
dim variable
format cell
variable = cell.value

Setting the cell format does nothing to help get a string into the cell.value - it only affects the appearance of the cell when you look at it. The value is still a number. OK?

The variables are not explicitly dimmed. So you have
Dim Begin_Of_Date

Instead of
Dim Begin_Of_Date As String
or whatever it should be.

I think I can not tell you exactly what the answer is, it will depend on the exact syntax you need in the SQL. So you may need to try a few things to get the right answer. It will, I think, depend on the data type of the date field.

I don't know the exact SQL that will work for you.

Some comments. In VBA to get a text string from a cell numeric value, something like this might be one way,

Code:

dim strBegin_Of_Day as string
strBegin_Of_Day = Format$(Range("J3").Value, "yyyy-mm-dd h:mm:ss.000")

There is also in VBA the TimeSerial function, please refer to VBA help.

I hope this helps a little.

Regards, Fazza

Ashk49 · Jul 2, 2008

Thanks again Fazza,
Yes ,You are absolutely correct .I believe your solution will make it true.
Actually I fixed it few days ago by one statement that is almost the same way you offer.

Rich (BB code):

Range("J3").Select
    Temp = ActiveCell.Value + 0.00001
    Range("J3").Value = Filter_Date
    Selection.NumberFormat = "YYYY/MM/DD h:mm:ss;@"
    Range("J3").Value = Filter_Date
    Begin_Of_Day = Filter_Date
    Begin_Of_Day = Format(Filter_Date, "yyyy-mm-dd hh:mm:ss")

Most of the time, easy solution ;but you need to know the way.
Just I don't know how to say thank you .Awesome guy

You learned me a lot and I owe you a lot.
Best Regards &Thanks
Ashkan

Fazza · Jul 2, 2008

Thanks for the kind words, Ashkan. You're doing very well yourself. Keep learning.

Regards, Fazza

Problem to Filter query

Ashk49

New Member

Excel Facts

Fazza

MrExcel MVP

Ashk49

New Member

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

Problem to Filter query

Ashk49

New Member

Excel Facts

Fazza

MrExcel MVP

Ashk49

New Member

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock