CODEWITHSUNDEEP
sqlms-accessvba
Peraklo
Peraklo

Reputation: 79

Insert textbox value into Access SQL query

Being an amateur in Access and VBA, I've hit a wall with the following...

In MS Access 2007, I have the following query:

SELECT .... WHERE format(NZ(l_f_date),'yyyy-mm-dd')<=**'2012-04-03'**);

I have shortened it a bit of course.

The database has approx 20 queries that need to be run on a daily basis. I have created 4 macros to run groups of queries in the manner that I need them to be run. The problem is that in every one of those queries I first have to change the date (like in the upper query). I am looking for the way to automate it a bit more.

I have an idea to create a Form, place a button for every macro on it and 2 textbox-es to enter the 2 dates I need. Now, I need those dates to appear in the bolded part. As I think about it, I have 2 options:

I think I can manage the first solution, but the second one is making my head hurt.

Can you help?

SQL from comment

select proc_desc,count(s) as broj 
into upit3 
from ( select distinct a.case_id as s,
          a.org_case_id,a.act_date as day,d.prod_id,d.prod_desc,
          c.fname,c.dpd,c.due_amount,proc_id,proc_desc 
       from actions_b as a, cases_old_b as c,processes_b as p,
            product_dict_b as d 
       where a.org_case_id=c.[org_ case_id] 
       and a.act_date=Forms!Form!Text10 and d.prod_id=c.product 
       and p.proc_id=c.process and not_lead=1 ) 
group by proc_desc order by proc_desc;

OK, sample data....

In x.dates, value is exactly like this: 03.04.2012
In a.act_date value is like this: 01.07.2011 13:53:56

so if its not possible with this values as they are, is it possible to use a LIKE statement in the query? Pseudo: WHERE a.act_date LIKE x.date%
If its possible, how to use it? i am a novice in sql and access queries... I google but sometimes, like this time, i get stuck.
Thanks

Upvotes: 1

Views: 11834

Answers (3)

ali
ali

Reputation: 1

I Solved this problem with : [Software: MS Access 2013]

CurrentDb.Execute "INSERT INTO [Inventory Transactions] ([Product ID], [Order ID])" _
& "VALUES ( " & Me.Product_ID & ", " & Me.Order_ID & ")"

Upvotes: 0

Peraklo
Peraklo

Reputation: 79

OK, so i decided to create a simple 2-column table in my database that will be used just for storing yesterdays and todays date... i added 2 text-boxes to a form, 1 button and added the next procedure to the button:

Private Sub Command25_Click()
CurrentDb.Execute "DELETE * FROM Datumi"
Dim tbl As Recordset
Set tbl = CurrentDb.OpenRecordset("Datumi")
tbl.AddNew
tbl!brojka = "1"
tbl!datum = Text8.Value
tbl.Update
tbl.AddNew
tbl!brojka = "2"
tbl!datum = Text10.Value
tbl.Update
End Sub

As you can see, the click on the button will clear the table and store new values into 2 rows... row 1 is yesterday, row 2 is today...

And after that im pulling the values from that table like this:

... where x.brojka=1 and format(a.act_date,'yyyy-mm-dd')=format(x.datum,'yyyy-mm-dd') ...

I'm sure it can be done better but this one works for me...

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91306

This structure:

SELECT .... WHERE format(NZ(l_f_date),'yyyy-mm-dd')<='2012-04-03');

Is not a good idea. The general rule is to try and stick to field (column) names on the left side of the equals. So

SELECT .... WHERE l_f_date <=#2012-04-03#

Nulls will not be included and I hope your dates are dates and not strings.

Next add the form:

SELECT .... WHERE l_f_date <= Forms!TheNameOfTheForm!TheNameOfTheTextbox

EDIT re comments

You are using the query design window, yes? Please try this test query:

SELECT a.case_id, a.act_date
FROM actions_b AS a
WHERE a.act_date=Forms!Form!Text10

Upvotes: 1

Related Questions