Queries :: Basic Date Selector Query

May 10, 2013

I'm attempting to create a very basic database for a school project. It is a property management database, and I am attempting to create a query that selects properties listed within the last 45 days. I'm unsure how to code this in the build field of the query.

Field: Date_Available
Table: Property_Table
Sort:
Show: (Ticked)
Criteria: (This is the part I am trying to code but am unfamiliar with the syntax)

View Replies


ADVERTISEMENT

Queries :: Undefined Function In Query And Visual Basic

Apr 3, 2014

I'm doing some debugging over the phone.My friend has Access 2003.What does not work:When he codes the MID function in a query he gets an error like this:"Undefined function 'Mid' in expression."

In the Visual Basic editor, he gets the same error: MID is undefined.What DOES work:I walked him through the use of LEFT and RIGHT in the query and they work.In Excel, MID works.Might there be some odd ball Access configuration mess up, or maybe more likely, his Access installation is incomplete.

View 3 Replies View Related

Queries :: Date / Time Query - Return All Records Of Specified Date Or Date Range

Aug 19, 2015

I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.

I have tried

Code:
Between [StartDate:] And [EndDate:]

And

Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"

Neither of which work ....

View 13 Replies View Related

Creating Basic Queries

Feb 15, 2006

I need help setting up some basic queries on my database, i dont really know what fields im meant to be querying so help would be great.


Sample data Included in attachment.

In the sample, seconds is the time step, so 5 minutes would be 300 seconds.


Queries i need to create:

Query 1 - Number of spill timesteps >0.001
Query 2 - Total number of timesteps

View 3 Replies View Related

Queries :: Basic Delete Insert Select Statement

Nov 8, 2013

what is wrong with the following statements. I'm new to access SQl. This would work in ms sql.

Code:
DELETE from Table1
INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, '10/10/2012','10/10/2012')
Select * from Table1

View 1 Replies View Related

Queries :: Set Up Query Which Responds And Filters By Date (Older Than X Date)

Jul 11, 2014

I currently have a query set up which responds and filters by the date.

I use the current code in the criteria on the query for todays date less 7 days

Code:
DateAdd("d",-7,Date())

This works perfectly and I had no problem with it. However I need to adjust the criteria so it is less than 7days ago OR OLDER....

How would I go about doing that?

View 4 Replies View Related

Queries :: Date Field With Time - Query For Date Only And Get All Records

Apr 26, 2013

I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.

View 11 Replies View Related

Very Basic Query

Sep 20, 2005

I have written a basic query that looks at the date field, department field and cost the cost field on a table. What I would like is a form that you can chose a date (19/09/2005) and a it looks at the table and calculates the total sum of money for each department for this date. Ie.

Date: 19/09/2005

Dept 1 = £123.00
Dept 2 = £1234.00
Dept 3 = £123.00
Dept 4 = £1234.00
Dept 5 = £123.00
Dept 6 = £1234.00

I can get the query to work but only 1 department at a time. Any ideas?

I have attached the DB

Thanks
D

View 6 Replies View Related

Basic Sql Query

May 22, 2006

Hello all This is a pretty basic query I guess but I am having trouble solving this.. Can anyone help plsssssssss.
I have two tables
Table1
Code Serial_No Amount1 Amount2
1 ABC 100 200
1 ABC 720 220
4 ABC 150 112
9 PQR 11 25
9 ZZZ 122 44
9 ZZZ 1250 42

Table2
Code Serial_No Amount1 Amount2
1 ABC 24 20
1 PPP 72 22
4 ABC 15 12
9 PQR 11 54
9 ZZZ 22 44
9 ZZZ 150 99
and total of the amount as 844 and 440

I have write a query to select only the Code common to both the tables and add total of amounts for these codes. For eg. I want rows
Outpput
1 ABC 100 200
1 ABC 720 220

1 ABC 24 20

Thanks in advance

View 4 Replies View Related

Queries :: Start Date And End Date Query?

Mar 26, 2013

I've attached a stripped down version of a small order database I'm working on.

A user would enter an order, the amount and the date the order is required by.

As you can see from tbl_seasons, the business has financial periods that match the first and last 6 months of each year. Each season has a start date and end date.

What I'm trying to build are two queries:

1. A query which lists all orders and has an extra field which shows the "season_id" that the order (date) relates to (based one the start date and end date in tbl_seasons)

2. A totals query which shows the total order amounts by season

how I might build these 2 queries.

View 2 Replies View Related

Help Building A Basic Query...

Sep 20, 2005

OK, here's the deal. I have two data tables, one of which holds many types of customer information and has a very large number of records, including, name, address, phone, and email. The other table contains only email addresses. Each email in the second, smaller table corresponds to a record in the larger list , by virtue of both records sharing the same email address (primary key?). I need to be able to take a short list of email addresses from one table and produce all of the records from the larger table that have corresponding email addys, essentially. Can somebody shed some light on how I can structure a query to solve this dilemma? Sorry for the newb question, just getting started out using db's.. Thanks!

View 1 Replies View Related

Basic Query Question

Aug 10, 2006

Hello, i have a small database thats purpose is to show when a book has been returned to me. I am recording the date that books are loaned out. Books are loaned for a 2 week period. I dont know if it would be easier to just manually put in the date that books should be returned (so that means just manually calculate what the date will be 2 weeks later). The reason for this is because i want a query that shows when the date has passed and how many days overdue the book is. Would this be difficult to achieve? What i was thinking is a query that calculates when the 2 week period is up and adds the unique number for book in a report.

Can anyone help?

Thanks,
Marley.

View 2 Replies View Related

Basic Query Question

Nov 28, 2006

i have a very basic query question that i can't seem to figure out.

i have two tables that have a one-to-many relationship - one has patient ID's, and the second table has test results for each patient.

i would like to query for all positive test results - however, if a patient EVER had a positive result, i want ALL records for that patient returned.

thus, if a record in the "many" table meets a particular criterion, i want it to return all records with the same ID. does that make sense?

thanks in advance...

View 6 Replies View Related

Basic Update Query

Jul 11, 2007

hi..

quick question...

i want to update all the records in a certain field..

to make the first letter of each word a capital

to make every letter a capital

i know how to make un update query, what to i put in the criteria if the field is called

field1

cheers

View 5 Replies View Related

Queries :: Date Query To Match Other Date Query?

Nov 3, 2014

running 3 queries together.

1 qry has a date parameter of start/end date(running before the 3 are together)

Have a field in 1 of the 3 queries with a date field that I need to show being <= the date parameter...

How/where do I use this criteria?

View 3 Replies View Related

Can Someone Please Explain Basic Query Logic To Me?

May 11, 2006

I am not sure if I understand this...

I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????

Any help is greatly appreciated.

Tom

View 4 Replies View Related

A Basic Delete Query Request

Jun 5, 2006

Hi All,

I have two tables; "Section_Failure_Mode" and "Risk_Assessment". They are linked by a common field called section_failure_mode_id. I would like to write a query where you can delete every record in "Risk_Assessment" where the checkbox (called Selected) is not selected (i.e. equal to 0) in the table "Section_Failure_Mode". However, I do not want any records to be deleted from "Section_Failure_Mode", only from "Risk_Assessment".

I have put a link to the screen cap of the query I have tried below. Is this the correct way to do it?

13797

Best Regards,

Aaron

View 1 Replies View Related

Using Access Query In Visual Basic?

Sep 18, 2006

I have an access Query(named newSerial) :
SELECT TOP 1 (Productbase.Serialnumber+1) AS Expr1 FROM ProductBase
ORDER BY (Productbase.Serialnumber+1) DESC;

I want to access this new value. Its not a key since an autonumber may be any number. When I create a new product. How do I get this value inside a form?
It works fine when clicking on it. But if I use an unbound box I get 1 as result and not like 76067 which it should be. If I click the query in access it works fine and show 76067.
I tried:
outbox=Expr1
' gives null
'or
outbox=[newserial].[Expr1]
' which gives "Access cant find the field "|" refered to you in your expression"

What should I write? Whats the correct expression?

View 7 Replies View Related

Basic Access Query Question

Sep 10, 2004

Is it possible to use a value on an Access driven form as a parameter in a pass through query?

View 4 Replies View Related

Visual Basic Delete Query

Oct 20, 2004

Hello,
I am hoping someone might be able to help me out. I am trying to create a delete query in VB for access.
I have managed to get it to run the delete query, but I would like to program in the responses to the following questions:
"You are about to run a delete query that will modify data in your table. Are you sure.... Yes"
"You are about to delete X row(s) from the specified table. Yes"

Here is what I have so far:

Private Sub Command32_Click()
DoCmd.OpenQuery "DELETEQUERY", acViewNormal, acEdit
End Sub

Does anyone know how to do this???
Thanks,
Jason

View 2 Replies View Related

Basic Query Form, Foreign Key Field

Oct 18, 2006

hello.. i have 2 tables

CUSTOMER TABLE
customerid
customername
customerphone


BOOKING TABLE
bookingid
bookingdate
customerid


At the moment i have 2 forms.. one customer form based on the customer table and one booking form based on the booking table.

at the moment, my user enters a customer record using the customer form.. they then save and close the customer form..

they then open the booking form.. and in the customer id field they enter the customers id.. this way, they successfully register that customer to that booking..


what i want to do is allow the user to enter the customer details and the booking details in one form..

i assume i would have to create a third form based on a query..

if i include all the fields from both tables in this query.. and then make a form based on this query, how can i make the following 2 things happen..?

1. when the third form is opened and customer details are entered... a new customer record will be added to the customer table (with a new customerid)..

2. this id will then need to be auto placed into the foreign key customer id field in the booking table.


do you get me?

View 3 Replies View Related

Record Selector

May 9, 2006

Hi

Does anyone know how to remove the "Record ID" from the record selector? I want to be able to traverse from one record to the next, however I do not want the field representing what record it is on showing up ( if that makes any sense).

Thanks

View 3 Replies View Related

Remove Record Selector

Mar 22, 2006

How do I remove a record selector from a subform? I went to the actual form for the subform and went into design view. I changed the record selector to NO and it is still there.

View 2 Replies View Related

Record Selector Sync

Aug 27, 2006

I have a table with fields ID, NAME, ITEM, QUANTITY and DATE_IN. I have a query "qry_Logged_In" based on the table. I then have a form based on the query.

I created a combo box as a "go to record" selector and used the following Row Source:

SELECT DISTINCTROW qry_Logged_In.ID, qry_Logged_In.NAME, qry_Logged_In.ITEM, qry_Logged_In.QUANTITY, qry_Logged_In.DATE_IN FROM qry_Logged_In ORDER BY [ID];

On the After Update proceudre I have the following code:

Sub Combo135_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo135]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

It works fine, but one thing I would like is that as I toggle through records using the record selector (forward/backward) buttons, I would like for the record number in my combo box to also change keeping in sync with the record number. Unfortunately, it currently stays to the number I last selected in the combo box.

Any suggestions on how to make the record number showing in my combo box to change with the record number?

Thanks,
Jim

View 7 Replies View Related

ActiveX Color Selector ?

Nov 30, 2004

For selecting a Date /time value, I use activex control DTPicker5... What Activex object, or infact any object, can I use to select a color value ?

your answers will be appreciated

Jabez

View 2 Replies View Related

Independant Selector Controls Per Sub-form Row

Jul 12, 2005

I'm new to Access so apologies in advance if this occurs as simple-headed...

Given a group identifier (e.g. department) I want to display a list of members (e.g. employees) in that group. So I use a sub-form in datasheet view.

Now I want to associate an independant checkbox with each row so that I can identify partiuclar members for some action (e.g. assign to a project).

Thus, I need to set up the checkboxes on each rsub-form ow and then be able to probe them later and get their state and any other info on the row (e.g. key(s) for the individual members).

All help / advice / references welcome!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved