Creating a date/time search function in Postgres.

[Amish_Ninja]

Hi all,

I have a question about how a certain function would look. Basically, what I want to achieve is a function that would take in a 'from' date (day, month, year) and a 'to' date (also day, month and year) and then spit back a series of records whose timestamps fall between those two dates (in essence, a select statement with variables). How would a basic function like that look like? Would I need to have separate integers for the day month and year, or is there a date/time object I could use instead? I am somewhat new to Postgres, but not SQL altogether.

To be specific: I got stuck doing a small job using Access to connect to a Postgres database. I need to take two dates from an Access form, and plug them into a function/procedure that will get what I need out of the Postgres database. I suppose I could use VB (ugh) to create a select statement to be fed into the Postgres database, but I get the feeling that it may be more of a hassle than creating a Postgres function, you know, since it's VB.

Thanks in advance. 🙂

[Installer]

I don't know PostgreSQL real well, but from a quick look at the manual I'd say it'd be the same as MySQL:

$query = "SELECT * FROM my_table
          WHERE date_column BETWEEN " . $date_1 . " AND " . $date_2;

(Answer composed before your second paragraph was added. Still should work.)

[Roger_Ramjet]

What are you using to connect to the postgre db - ADO, .NET, ODBC?

Whatever it is, in Access you can create a query that will referance the form input controls directly, so you don't need to construct the sql string.
Just go into query designer, add the tables you want to query and the fields you want your query to return.
Then right click on the criteria below the date field and select Build. This will take you into the Expression Builder.

[edit: got the task order wrong first time]
Expand the Operators section -> Comparisson -> Between
You will see: Between «Expr» And «Expr» in the top box.
Select the first <<Expr>>
Expand the Forms section and navigate to the form that has the input date controls. Select the first form field and it will replace <Expr>>
Do the same for the second <<Expr>>
[end edit]

Finally click OK.
Your query will now be built with the WHERE clause referancing the Form controls; it will look something like this in sql

SELECT * FROM table WHERE datefield BETWEEN Forms![frmDate]![txtStart] AND Forms![frmDate]![txtFinish]

Save it with a meaningfull name and you can run it with a command button on the input form - again Access's inbuilt Wizards will do most of the code building for you when you add the command button to your form if you so desire.

I dunno what so many people have against VB, it's as good as any other language for most things and a lot better than some. PS. Access uses VBA which is a very different thing from VB.

[Amish_Ninja]

In general I just don't like the syntax of VB. I prefer OO languages like Java and C++, and web-based languages like PHP and Ruby. It's also a lot less typing with those languages, and I feel that they are considerably more robust.

With VB (or VBA in this case), I can't create an array and populate it on the fly. I have to create the array, and then add things to it afterwards. Same thing for declaring variables. I can't say String blah = "hi", I have to declare it first with 'Dim blah As String' and then on the next line say 'blah = "hi"'. All in all, I despise the language, but I have to use it for my current project.

Thanks for the help. I'm going to opt for building the query as a String after pressing a button. Do you know how to call on the query from the VBA code itself rather than using the form controls? Let's say I wanted to fill a subform with the results of the query I made in VBA.

[Roger_Ramjet]

You only have to declare vars if your module has 'OPTION EXPLICIT' at the top - that applies whether it is a class module or a general module, and only applies to that module. If you do not use option explicit then you can implicitly declare any var by just using it in an assignment statement.

To use the query as the datasource for a subform you set the subform's Record Source property to that query.

From Access Help - which you should learn to use as it has excellent example code

RecordSource Property Example

The following example sets a form's RecordSource property to the Customers table:

Forms!frmCustomers.RecordSource = "Customers"

The next example changes a form's record source to a single record in the Customers table, depending on the company name selected in the cmboCompanyName combo box control. The combo box is filled by an SQL statement that returns the customer ID (in the bound column) and the company name. The CustomerID has a Text data type.

Sub cmboCompanyName_AfterUpdate()
    Dim strNewRecord As String
    strNewRecord = "SELECT * FROM Customers " _
        & " WHERE CustomerID = '" _
        & Me!cmboCompanyName.Value & "'"
    Me.RecordSource = strNewRecord
End Sub

That last example is for a single record in a form that has it's Default View set to Sinlge Record. If you change it to Continuous Forms, or use a Datasheet view, then it can be populated with a multiple record query.

Easiest way to automate things would be to use the AfterUpdate Event Procedure of the text box that you are going to enter the second date into. Remember to also use the form's Requery method to refresh the display after any update.

[Amish_Ninja]

Actually, I didn't make the query with the query builder, I made it in the VBA code-behind since it has variables. It's not just a simple 'select * from customers" query. so far I believe I have the query result going to a RecordSet object. do you know how to bind the subform to that RecordSet in the code itself?

[Roger_Ramjet]

Can't be done. That is why the example uses the query string previously built in code.

Me.RecordSource = strNewRecord

[Roger_Ramjet]

Amish Ninja wrote:

Actually, I didn't make the query with the query builder, I made it in the VBA code-behind since it has variables. It's not just a simple 'select * from customers" query.

IF YOU READ MY FIRST POST PROPERLY YOU WOULD FIND THAT I DETAILED HOW TO BUILD A COMPLEX QUERY THAT USED FORM VARS. :mad: And I spent quite a lot of time writting that reply so yes I am mad that you cannot be bothered to read it.

[Amish_Ninja]

Roger Ramjet wrote:

Can't be done. That is why the example uses the query string previously built in code.

Me.RecordSource = strNewRecord

See, this is another reason why VB, VBA and Access can go die in a fire.

IF YOU READ MY FIRST POST PROPERLY YOU WOULD FIND THAT I DETAILED HOW TO BUILD A COMPLEX QUERY THAT USED FORM VARS. And I spent quite a lot of time writting that reply so yes I am mad that you cannot be bothered to read it.

I read it. I just figured that since a fine and experienced individual such as yourself defends VBA/Access so vehemently, that it might be... I dunno, MORE FLEXIBLE?

I don't just need to create a query with some form variables, is the problem. I need to take multiple form inputs (dropdown boxes), take their values, and turn them into a date/time string that can be used by a 'select between date and date' query. So I guess now I have to do that part in the code and write them to invisible labels or something ridiculous, and then put THAT into a query in the query builder. Again, Access dying in fire = happy time 4 me!

Why anyone would prefer to develop with this toolset is beyond me. It's torture.

[Roger_Ramjet]

As I said Access uses VBA and that is a whole other thing to VB. What is possible in full programming languages like C++, PHP, VB, etc is not possible in VBA. With the release of Visual Studio 2005 VB# and C# are almost indistinguishable, and the runtime code either generates will be the same.

Now people use Access because it is very quick to develop and modify in, but it can be very dirty if you are not experienced with it and it's flaws.

It is in the nature of Access Forms that you cannot set the datasource to a recordset that in already resident in memory. (You certainly can do that in VB but not in VBA). The form has to create it's own recodset in memory. You cannot even work on that recordset directly in code but have to work on it through the form's own methods - but then that should be what you expect with OOPS anyway: remember a Form is a class object.

As to the variables that you want to be able to input through your form - again the example that I gave shows you how to build a query that uses form input from a Combo Box (a dropdown box to you). In the example it is an integer ID populated from the database itself. It could equally be dates populated any way you want. If you want to use a calender control then there are lots of them available in Access as well.

If you are using Access then drop the attitude and learn about it. I personally have always thought of it as a piece of crap, but it is what the customer wants so often that I have become and expert despite my opinion - and believe me when I'm coding and Access starts to play it's usual MickeySoft madness then anyone within 200 yards knows just how much I hate the thing.