Date range to data grid? 3

[mdctsg]

I have a database (access 2000). In the db has a query Address Corrections. Currently I am pulling the info into a data grid. In the db there is a column called Bill Date. The query and some of the fields have spaces between the names. What I would like to do is pull the info into the grid based on the Bill Date. I have been searching for help but haven't found an answer. The search function on this site has been down for a bit. Can anyone help me. I have no ideal how to use the date picker function but would love to incorporate it into my program.

Thanks
Bob

 

[johnwm]

Under VB6 just add MS Common Controls 2 (mscomct2.ocx) in Project|components. The DT picker will appear in your toolbox.

Use the VBHelp for usage examples to get you started

If you are using dates in an Access query, don't forget to add the date delimiters (# signs) as in:

strSQL = "Select * from tblDates Where fldDate = #" & Dtpicker1.value & "#"
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

>strSQL = "Select * from tblDates Where fldDate = #" & Dtpicker1.value & "#"

Again, will not work on different locals.

You need to format the DatePicker value, and at best ANY date, when passing it to a SQL Statement:

Public Const gcSQLDATE = "\#mm\/dd\/yyyy\#"

strSQL = "Select * from tblDates Where fldDate = " & Format$(DateClicked, gcSQLDATE)

If the date field includes time other than 00:00:00, and time is also a factor in the criteria, then the time needs to be also included into the format. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[johnwm]

You are, of course, correct CCLINT

I was trying to oversimplify. mdctsg has asked 26 questions in the forums, and has not marked any as being helpful. I deduce that the answers need to be kept at a basic level, then amplified as and when necessary.

Mea Culpa
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

I probably wouldn't have mentioned anything if I wasn't seeing so many problems with this (date criteria in SQL statements with-in different locals/formats).

Then...consider it done for the sake of others who read this [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[mdctsg]

Gentlemen I wasn't aware of the marking post as useful function. My mistake. I am sorry if I have done anyone wrong. I wasn't my intent to be rude or disrespecful to anyone. I do greatly appreciate the help offered to me by you and those who reply.

Again I am sorry
Thanks
Bob

 

[CCLINT]

No you aren't rude.

Some of your other posts show that you responded with a "Thank You", and informed if the posts have helped.
It helps others to know what answer worked, and that an answer worked in solving the problem....and shows respect to the ones who spent time to help.

IMHO, that is the main concern here.
Make sure that you always do this.

If you feel that the answers can help others, especially if it is an unusual problem or a interesting/new idea/logic then marking the post throws up a flag for others to take additional notice of.

Also using it as a sign of "completion", that some answer did help or worked, would also have it's positive value as well.




[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[mdctsg]

I tried putting this into a module but it didn't work
Public Const gcSQLDATE = "\#mm\/dd\/yyyy\#"

And my statement

strSQL = "Select * from [Address Corrections] Where [Bill Date] = " & Format$(DateClicked, gcSQLDATE)

My query has spaces in the names and so do the fields

I am sure I'm doing something wrong. I have 3 VB books and none of them touch on the dtpicker other than to say what it is. But thanks so much for trying to help me

Bob

 

[mdctsg]

Also I am using a ADODC to populate the Data grid if that makes a difference

 

[johnwm]

Is your Bill Date field actually a Date field or are you storing strings?

CCLINT's code should be fine if your field datatype is Date format

Oh, and I wasn't implying any criticism in my earlier comment, but often the quick way of seeing if previous answers are pitched at the right level is to check for 'helpful answer marks'. If I see lots of 'questions' and only a few 'helpfuls', I will try to simplify the answers given

CCLINT has done the right thing, and actually looked back through your individual posts to find your verbal 'Thank Yous'
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

>I tried putting this into a module but it didn't work

So...what is happening, or not happening?
Nothing?
Error?

Post the code where you are opening the recordset or refreshing the ADODC, and, a sample of the string:

?"Select * from [Address Corrections] Where [Bill Date] = " & Format$(DateClicked, gcSQLDATE)

as printed out in the immediate/debug window. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[mdctsg]

This is what I am doing

Option Explicit
Dim gcSQLDATE As String
Dim DateClicked As String
Dim strSQL As String
Dim mstrConnectionString As String


Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
DTPicker1.Value
End Sub

Private Sub DTPicker2_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
DTPicker2.Value
End Sub

Private Sub Form_Load()

mstrConnectionString= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\UPS Invoice Tool.mdb;"
dcexport.ConnectionString = mstrConnectionString
dcexport.Visible = False
dcexport.RecordSource = "Select * From [Address Corrections] Where [Bill Date]= " & Format(DateClicked, gcSQLDATE)

dcexport.Refresh

End Sub


I'm getting a 'Syntax error (missing operator)in query [Bill Date] ='
Method Refresh of object IAdodc failed

The query is [Address Corrections] but it is calling the field the query.

Thanks for taking a look at it
Bob

 

[mdctsg]

The column is formatted as date/time also

 

[mdctsg]

Gentlemen

I figured out last night what I was doing wrong. I wasn't declaring the dtpicker. For some reason I thought it was running on it's own.(Greenhorn)

In my search I did notice others who are having the same problem as I did so I will post what I did in hopes it can help someone else. I was using a data grid and adodc. I went back to the adodb and used the flex grid. I had used this before just to populate and was able to get what I wanted.

Option Explicit
Dim WithEvents cn As ADODB.Connection
Dim WithEvents rs As ADODB.Recordset

(This will load the data in the flex grid after the dates are picked.)

Private Sub Command1_Click()
Dim fd As Date
Dim ld As Date
fd = DTPicker1.Value
ld = DTPicker2.Value


Dim strConnect As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\mydb.mdb"

Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open strConnect

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockPessimistic
rs.Source = "SELECT * FROM [Address Corrections] WHERE [Bill Date] " & _
"BETWEEN #" & fd & "# AND #" & _
ld & "#"

rs.ActiveConnection = cn
rs.Open

Call LoadFG

End Sub


Private Sub LoadFG()

fg.AllowUserResizing = flexResizeBoth
fg.Cols = rs.Fields.Count + 1
Dim i As Integer
fg.Row = 0
For i = 0 To rs.Fields.Count - 1
fg.Col = i + 0
fg.ColAlignment(i) = flexAlignLeftCenter
fg.Text = rs.Fields(i).Name
Next
Do While Not rs.EOF
fg.Rows = fg.Rows + 1
fg.Row = fg.Rows - 1
For i = 0 To rs.Fields.Count - 1
fg.Col = i + 0
fg.Text = rs(i).Value & ""
Next
rs.MoveNext
Loop
End Sub

(This clears the data from the cells so you can requery)

Private Sub Command2_Click()
fg.Rows = 1
End Sub

Private Sub Command3_Click()
Unload Me
End Sub

The only thing I didn't like about using this before is the grid showing up before the data is loaded. I could never figure out how to hide it.

CCLINT

You did mention something about not working on other locals. I am wondering about that. Once I complete this it will be used in the US and Overseas. I'm not sure how the days will act in that manner. I will mark both of yoy comments as useful because you have both helped trememdously. I am not sure how to close this or let others know about it. Is that in the faq?.

 

[CCLINT]

mdctsg:

You have done all that you need to do:

1. Responded with acknowledgement (thank you)

2. Responded with the solution which solved the problem.

You do not need to close anything or do anything further in this respect.


>something about not working on other locals

If you format the date as shown, when passing the date to an SQL Statement, you shouldn't have any problems on other locals.

However, on other locals you would NOT format the date this way when displaying a date to the user.
Any hard coded dates need to be in US format.

Any date variables used in code (Either a date held in a date variable (Dim TheDate As Date), or a string date input, such as from a textbox, converted to a date using the CDate(), function) should work fine.

When displaying the date stored in a date variable, or using the format function, to a user, the date should automatically format to the date format set in the system settings. If you want another format (such as a 4 digit year when the short date format in the system settings is set at 2 digit), then use the format function.

This doesn't cover everything, but should get you started off in the right direction.
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

One other thing:

Using the date seperators ( #, as in #TheDate# ) will vary from provider to provider.
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[johnwm]

mdctsg,

Thank you for acknowledgement. You last post is an example for many others in the forums to learn from!

CCLINT
Hear, Hear!
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'