Does Setting Validation In A Query Possible?

Oct 5, 2004

I'm making a database with 50 fields for the shops of my company. Among these fields, the data of 20 fields must be submitted to us (head office). The data of the remaining 30 fields (like customer address) is just for generating receipts.

At the begining, I set the validation rule of that 20 fields to be "Is not null" in the Form. If the required data (20 fields) is not entered, messages will appear and the record cannot be saved. Then I use the Query function to extract that 20 fields. However, our staff said they might not be able to obtain the information of the 20 fields in the first time. The customers may send them the info later on. So, it not good for me to set any validation rule in the Form.

I would like to ask how I can ensure they submit the data of the 20 fields if I release all the restrictions in the Form? Could I set any validation in the Query so that error message will appear if not all the required fields are filled?

Thank you.

View Replies


ADVERTISEMENT

Setting Up Simple Validation

Mar 11, 2006

I have some forms and I need to prevent erroneous data from being entered which for me, is basiclly anything that is not a letter or a number (so stuff like !"£$%^ is what I want to be prevented)

can anyone help me set this up? im pretty stumped!

View 2 Replies View Related

Setting Validation Rule For Percentage Column

Nov 23, 2013

I am stuck at setting a validation rule for my percentage column. The entries in such column must be less than 20 and greater than 10.

View 9 Replies View Related

Setting Up A Query

Mar 27, 2006

Hi,
I have a table I am trying to set up a query. I got into the query and set up the fields as follows: Name, Address, Landlord, Lease Date, 90 Day Reminder, 30 Day followup.

I want to show all these columns in my query. I need to set up formulas for the 90 Day Reminder and the 30 day followup from the Lease Date field.
The 90 Day Reminder is the date 90 days prior to the Lease Date, and the 30 day followup is 30 days after the lease date.

I am having trouble setting up the formulas. I did it in design mode, and I tried filling in a formula and when I tried going into the dababase mode, it asks for for a parameter. I am having difficulty understanding what to fill in for the parameter. I am not sure about the formulas either.

Can someone help me???

Thanks,

Marilyn

View 1 Replies View Related

Need Help Setting Up A Crosstab Query

Oct 11, 2005

I have a table with 20,000 names in column 1.
Columns 2-8 are Yes/No questions.
I would like to set up a query to see how many people answered Yes for any combination of questions. I think a crosstab is needed, but I just can't for the life of me think how to do it.
Any ideas?

View 3 Replies View Related

Setting The Record Set Of A Query

Aug 15, 2006

Can i edit a query to equal a SQL string just like i do for my reports? for my reports i say:


DoCmd.OpenReport "Check Filter", acViewDesign, , , acHidden
Reports![Check Filter].RecordSource = strRecordSource
DoCmd.Close acReport, "Check Filter", acSaveYes


i tried to say

DoCmd.OpenQuery "Query1", acViewDesign, acEdit
Queries![Query1].RecordSource = strRecordSource
DoCmd.Close acQuery, "Query1", acSaveYes


i get an error in the middle line...how do i do what i'm trying to do?

View 2 Replies View Related

Setting A Value In The Results Of A Query

Dec 1, 2006

I have made a query and i want a tickbox to be ticked on the results.. (an escalated tickbox)

for some reason i cant make a macro with the following:

OpenQuery
SetValue

The query i'm using to detect if something needs escalating is quite complex, which is why i cant use an openform or similar?

Any help would be appreciated. James.

View 2 Replies View Related

Help Setting Criteria Within Query

Mar 11, 2008

Hello, I am working on a database to analyze weekly purchase results. With the goal being to determine the response rates for each promotion source that was used.

I have a table that contains PromoName, PromoCode, PromoQuantity, PromoSource and PromoDropDate. In another table I have imported 8 weeks worth of results and use the fields PromoCode and DatePurchased. An additional field called TransactionValue is auto assigned to the number value of 1.

I was able to create a query that looked at all the records with the same PromoDropDate and PromoSource field and then summed the values in the TransactionValue.

I would like to be able to see the results by week for 10 weeks but am not sure how to proceed. For example if the PromoDropDate is 1/10/08 I could look at the results based on DatePurchased being between 1/10/08 and 1/16/08, and then for 9 week periods after that.

I welcome any and all suggestions on the best way to approach this. thanks.

View 5 Replies View Related

Setting Query Value From Form

Nov 11, 2013

I am trying to access the same query/report from different forms. For example I have a StudentAttendanceForm where users select a StudentID and then open the report. The query criteria for ID# is [Forms]!

[StudentAttendanceForm]![StudentID].

Can I access this same report from an event on another form. I'm thinking that if I can some how assign the StudentID from the form I want to access the report from, to the criteria on the StudentAttendanceReport Query, this is possible.

I am new to code but I am thinking something like:

SET [StudentAttendanceQuery]![StudentID] TO [StudentEnrollmentForm]![StudentID]
Open StudentAttendance Report

The StudentEnrollmentForm is the other form I want to access the report from

You may need a crystal ball for this one. Let me know what other info you need.

In general I am finding I am copying and tweaking queries to meet specific user needs. For example some want to access a single student and other need "batch" reports.

View 1 Replies View Related

Validation Query

Oct 24, 2005

I am creating a database for the local arts centre here is my question

we have several venues each with a limited capacity i am trying to create a booking system so when a venue is selected it checks for capacity at the venue. This is a one off event with one event being held at each venue so it does not need to take into acount dates just free capacity at a venue. Help

View 2 Replies View Related

Setting Criteria Within A Select Query

Aug 23, 2005

I have set up a query to pull data from within a date range.

I have written an SQL Statement to amend the format of the date field:

Effective Date: IIf([Date_Effective_From]="00000000",Null,DateValue(Mid([Date_Effective_From],7,2) & "/" & Mid([Date_Effective_From],5,2) & "/" & Mid([Date_Effective_From],1,4)))

Within my criteria I have:

Between [Forms]![Benefits]![date1] And [Forms]![Benefits]![date2]

(Benefits being the form). If I enter date range 01/01/2005 to 01/05/2005 I receive all data from 01 January to 01 May - but including different years i.e 2002, 2003, 2004 etc.

Can anyone suggest anything I may have missed?

Thanks

View 1 Replies View Related

Setting Parameter To Query In VBA Code

Mar 16, 2008

Hello Friends,
I have Query1 that should get one parameter , how to set this parameter in a code.

Below the code.


Private Sub Command0_Click()

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Query1")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
'
Do While Not MyRS.EOF

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailName]

With objOutlookMsg
.To = TheAddress
.Display
End With

MyRS.MoveNext
Loop

Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

View 2 Replies View Related

Setting Short Date In Query

Jan 17, 2006

I have a query where one field is Date:Now() so that todays date is forced in. However, it is giving me the date and time. I only want the short date, how do I achieve this???

Thanks!

View 2 Replies View Related

Evaluating Query Results And Setting A Value

Jul 25, 2007

hi

i have a query that returns a dataset that looks like this:

Account Status
123 Paused
123 Paused
123 Not Paused

I want to evaluate these results and in a second query, for the account 123 return the value "Not Paused" if there is at least one status set to "Not Paused".

How can i do this?

Thanks in advance.

View 13 Replies View Related

Setting Query Criteria In Code

Dec 13, 2007

I have a query that has 4 fields that are text fields in an external database. I have text boxes set up on a form which contain either null or >0 (depending on an option button selected). I want to pass that >0 to one field in the query and null to the other three. I have the following as criteria in my query:
[Forms]![frmSelectPayment]![txt2ndPayment] (same one for each of the 4 fields except the form field name changes)

Below is the code that sets the text boxes that are sent to the query:
Select Case [FraSelectPayment] 'depending on which pmt being pulled
Case 1 '6 payment plan only
stDocName = "qryGetPlusARImport"
Select Case [FraChoose]
Case 1
Me.txt2ndPayment = ">0"
Me.txt3rdPayment = Null
Me.txt5thPayment = Null
Me.txt6thPayment = Null
Case 2
Me.txt2ndPayment = Null
Me.txt3rdPayment = ">0"
Me.txt5thPayment = Null
Me.txt6thPayment = Null
Case 3
Me.txt2ndPayment = Null
Me.txt3rdPayment = Null
Me.txt5thPayment = ">0"
Me.txt6thPayment = Null
Case 4
Me.txt2ndPayment = Null
Me.txt3rdPayment = Null
Me.txt5thPayment = Null
Me.txt6thPayment = ">0"
Case Else
MsgBox "Please select a payment"
FraChoose.SetFocus
Exit Sub
End Select

DoCmd.OpenQuery stDocName, acNormal, acEdit


If I run the query alone I leave all fields blank and put >0 on the one I want to search by it works fine (695 records returned) but when I run it through code, nothing is returned. I really hope this makes sense to someone! Can anyone see what I am doing wrong?
Thanks

View 3 Replies View Related

Setting A Field In A Query From A Report?

Oct 6, 2004

I know its probably a simple one but I am new to access, so...

I have a simple membership database and in it I keep a record of who I am expecting to attend on a particular date. This is achieved through having a seperate field for each meeting on a simple yes/no type.

I would like to be able to print a report from a form for a particular date showing everyone that would be attending. I have a list box that shows all the fields. What I cant do is set the date I want in the query from the report, can anyone help?

thanks

View 2 Replies View Related

Queries :: Setting Zero Value In Crosstab Query

Jun 19, 2014

I have a crosstab query to summarise the counted string values from another query: E.g.;

TRANSFORM Count(Table1.Viable) AS CountOfViable
SELECT QryTable01.productName
FROM QryTable01
GROUP BY QryTable01.productName
PIVOT Table1.Viable;

As there are some null values returned (ie blank cell in the pivot table)

I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;

So if I add a text box in the report with = [viable] + [Not viable]
t
Then the report row with the values:

Viable Not viable
14 12
displays as 1412

So how do I add the values in the rows???

View 1 Replies View Related

Setting Default Value On A Form From A Query?

Dec 9, 2014

I have a combo box field on a Student Details form that displays the Student's class number. For a new record, I am able to set the default value in the control's Default Value property with a literal in quotes (ie., ="2015-1"). But I want to set the default value from a query field. However, when I specify the query field in an expression ((=[Current Class]![Class Number]), "#Name?" appears in the field when initiating a new record on the form.

The control source for is the field in the Students table ([Students.[Class Number]).

The Row Source is a query of the Class table.

How can I set the control's default to the Class Number value in the Current Class query?

View 14 Replies View Related

Primary Key/Validation/Query

Oct 12, 2006

Hi everyone,

Hopefully someone can help me.

In Table 1 - I have a set of contracts that have a unique number. However, although the primary key is the contract number - these numbers can be duplicated as long as their start and end dates don't overlap. So my question is, how do I make the Primary key the contract number plus the start and end date? Is this possible?

I know I can select multiple fields for the primary key, but this has a problem...

Here are 3 examples of how Table 1 could be constructed and the Table outline...

Table 1
Field 1: Contract Number
Field 2: Contract Start
Field 3: Contract End
Field 4: Contract Details

Records
Contract Number/Contract Start/Contract End/Contract Details


001/01-Apr-07/31-May-07/Items Cost £1
001/01-Jun-07/31-Jul-07/Items Cost £2
001/01-May-07/30-Jun-07/Items Cost £1

Now, if I select Contract Number/Contract Start/Contract End as the primary fields, the above would not create any conflicts. However, the third record overlaps the first two records date barriers - and I don't want that to be possible.

Any new record's start date MUST be after any existing record with the same Contract Number's End Date. Also, any new record's end date MUST be before any existing record with the same Contract Number's Start Date.

In Table 2 - I have a list of orders that relate to Table 1. i.e. Each order has a contract it relates to. How do I make sure that in a query, the order number picks up the correct contract for the corresponding date.

For example...

Table 1
Field 1: Contract Number
Field 2: Contract Start
Field 3: Contract End
Field 4: Contract Details

Table 2
Field 1: Order Number
Field 2: Order Date
Field 3: Contract Number

My query would pick up Order Number, Order Date, Contract Number, Contract Details.

I think what I'm looking for is validations in the fields. But I'm not entirely sure how to implement those.

I hope I'm making sense...

Thanks again for any assistance,

Ross

View 6 Replies View Related

Help With Validation Rule In A Query

Aug 13, 2006

I’m trying to do something which should be very simple, I’ve added a new field to a table, but for some reason it won’t update the field when using the form. It’s quite a complex form and my Access abilities are limited so I would rather not create the form from scratch.

I’m trying to create a query that displays only entry’s which contain my new field. However since I have the above problem it doesn’t seem possible.
One way I see around this would be to create a rule in my query where only records were shown when the fields “price” divided by “number_of_players” = 12.5
Can someone please tell me how to write this validation into a query?

Many Thanks
Chris

View 2 Replies View Related

Data Validation Against Query

Apr 26, 2006

Hi - I have an inventory management database allowing stock to be moved between various subinventories. The user choose a transaction type and then details which goods to move. However at the moment they could enter a Quantity that is greater than that available in that subinventory.

So I have a query that gives me:
Part#
Subinventory
OnHandQty

The data entry form has a control source directly to the Inventory Table. I want to compare a text field [Qty] on a new inventory transaction (the person already having selected Part# / SubInventory from combo boxes) to the On Hand Qty.

I have tried Expression Builder in the Field Validation Property and I have tried some sql code on the AfterUpdate of the text box. I'm just not getting it.

Any help appreciated.

Cheers,
Darren

View 3 Replies View Related

Setting Query Search Criteria From A Form

Oct 30, 2006

Looking to be pointed in the corect direction,

having trouble using a text box on a form to set the search criteria for a particular field within a query.

Ideally i would like to enter the criteria in a textr box then click a button which sets the criteria and opens the query results in a report,

I have designed the query but cant which works if you go into the design and enter the criteria. the problem lies with getting the text box on teh form to set teh criteria.

If i use the expression builder to set the criteria to the same value of the "text" within the "text box" on said form the following happens,

If i open the query itself it opens a small window and displays the "expression" that i entered in the criteia box, above the data entry. the query does work this way but dont understand why the expression is displayed???
The text box on the form also becomes locked, not allowing data to be entered.

I have tried to link the query direct to the text box.
also tried creating a table which has data entered via form then linking the query criteria to a field in the table.

Could some one point me in the right direction please

many thanks

Cheers

watson

View 1 Replies View Related

Setting A Format In A Field In A Union Query

Jun 21, 2007

Hi everyone,

Please would someone be able to help me?
I have created a union query however, one of the columns, has not picked up the same format as it has in the tables. As in the tables it has this format
'00000'.

Please woud you be able to advise me how I can change the format on one of the 'columns' in my union query. As one column is 'numbers' and the other is 'text'. I need to change the number column so the format is '00000'.

Thank you in advance for your help.

Nats

View 5 Replies View Related

Simple Query Question: Setting A Field To A Set Value

Jul 6, 2007

Putting this in the field box in a query:

read or write: "r"

should create a field called read or write with a value of r in each record in the query, shouldn't it?

Why doesn't it, and how can I make it work?

View 2 Replies View Related

Make Table Query Setting A Primary Key

Feb 26, 2007

I am trying to create a new Table using a MakeTable Query

using the following sql:

Code:SELECT qCPPlannedStopsOnTargetTotals.WeekNumber, qCPPlannedStopsOnTargetTotals.Line, qCPPlannedStopsOnTargetTotals.Description AS Above, qCPPlannedStopsOnTargetTotals.[%] INTO mkCPPlannedStopsAboveFROM qCPPlannedStopsOnTargetTotalsWHERE (((qCPPlannedStopsOnTargetTotals.Description)="Above"))ORDER BY qCPPlannedStopsOnTargetTotals.WeekNumber DESC;

However i want the new table to have a Primary Key, (Week Number) can i set this as the table is made?

Please help

Andy

View 1 Replies View Related

Queries :: Blank Query After Setting Criteria

Feb 4, 2014

I've set up a form with a button to open a report based on the current name on the form. The idea is that as you look through the different pages on the form you can open a report for whatever one you're on and print it.

In my query I have a name field where I put the criteria: Forms!Formname.Textboxname

By clicking the button on my form I'm able to generate a report based on the name that appears in that textbox. It works great when I initially put it in but if I close the form (or query, or report) and open it back up it is blank.

Is this even possible with a text box? It seems like it when I open it, it has nothing to go off and that's why it's blank. I just don't know how to fix that.

View 7 Replies View Related







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