Date Problem - Part Constant - Part Now()

Nov 3, 2006

Hi everyone,

I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())

Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.

Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.

Many thanks,

View Replies

Tricky Question - Form Part Data Entry, Part Not

Dec 16, 2004

I have what I think is a difficult problem to overcome...

I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.

Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!

If this is not possible, do you know how I can acheive this?

Thanks

S

View 1 Replies View Related

How Do I Get Out Part Of A Date In A Query??

Jul 17, 2007

Hey People,

Is it possible to take out part of a date in a query and put it into a new column, e.g.

if one field is : "Date"... with entries such as 12/05/07.. etc,

is it possible to have another column "Month" that has the specific month in the date column.. e.g the one above will have "May".......

anyone have any idea about this...

cheers.

View 5 Replies View Related

Selecting Part Of A Date

Mar 9, 2005

I have a table with the field "Date". The date is in the format mm/dd/yyyy.

I would like to create a query that selects only the year of each record, and disregards the month and day.

Is this possible?

View 3 Replies View Related

Newbie Date As Part Of File Name

Mar 22, 2008

Everything works great but I want to save the backup file with a unique name, like EX: myfile INTx.xls where intx = the date "Now", maybe, am I looking at this wrong.
Thanks Bob

View 12 Replies View Related

Modules & VBA :: Calculate Part Information Difference As Per Date?

Nov 21, 2013

How to implement a functionality into my access database. The related tables required for this functionality can be found as an attachment.

When the information has been entered into the tables by the user, I would like to calculate the difference between the information for each part within the same table. The main columns for which the difference have to be calculated are: [RefObsInfo->RefSources] and [AltObsInfo->StatusAltSource] and [GenObsInfo->GenSources]. The values for these fields are restricted to 3 or 4 different ones. I also would like to show if the GenEstAvailable columns are changed, but that's for later.

In order to calculate this difference a query has to select for each Part the information related to the last date AND a date which has been specified by the user (with the previous date as max). For this the user is asked to give a from date, which she/he can select from a combobox. This combobox has a standard selected value which is the MaxDate-1, i.e. the previous date. The difference should be calculated from the previous date to the last date. I would prefer to give each (predefined) difference a specific action to show the user the difference in a matter of seconds.

View 13 Replies View Related

Queries :: Using Part Of Date As Query Criteria From Combo Box?

Apr 4, 2013

I have a query with a date field that is formatted mm/dd/yyyy. I have a combo box that is formatted as mm/yyyy. I need the choice from the combo box to be the criteria for the date field in the query. the combo box has to contain the month and year only (which it does now) and the query must return mm/dd/yyyy. I have tried a few statements and the closest I got was a between statement that added 30 days to the combo box selection but that's not really accurate.

View 2 Replies View Related

Save Report As PDF - Adding Date Retrieved From Query As Part Of File Name

Nov 6, 2012

I'm trying to save a report to my Desktop as a pdf - adding a date {that I am retrieving from a query} as part of the pdf file name.

I get a
Run-time error '2501' - The OutputTo action was cancelled.
(See screenshot).

Here's the code I'm using. So far - all I've tried to do is run the function from the Visual Basic Editor.

Function SaveReport()
Dim strSQL As String
Dim strPath As String
Dim strFilename As String
'Get the ReportDate.

[Code] .....

View 4 Replies View Related

Add 1 Year To Year Part Of Date

Mar 14, 2006

I have a query based on payment date which I have extracted the Year part as a seperate Field StartYear, but I want to now add EndYear which just adds 1 year to the StartDate. e.g. EndYear = StartYear +1. Anyone kow please I know i's proably simple but I keep getting syntax errors.

View 3 Replies View Related

My First Database Part II

Mar 13, 2006

I am having more trouble with the old database I am trying to make.

Can someone have a look if I attach it?

I don't think the ID fields in each of the three tables are working togehter.

Shouldnt they be the same ID number of each record for that person across the three tables?

Thanks.

View 2 Replies View Related

Part Number DB

Aug 8, 2006

I am trying to create a Part Number Database and want it to start at 100000 instead of 1. Can someone tell me how I can get Access to start there?

TIA.

View 4 Replies View Related

Try To Figure Out What The Last Part Of This Does....

Apr 7, 2008

First time on here and I am looking assistance with the last part of this code. Can someone tell me what this is looking for thanks...

ExlFile.Application.activeworkbook.SaveAs "........Compliance Reports" & Rtn & BU & "-" & Cat & ".xls

View 2 Replies View Related

Updating Only Part Of The Value

Jun 29, 2006

I have a field called images and its name is like abc0001, abc0002 etc, i want to update all the abc to abcd0001, abcd0002 etc, how can I do this using update query or is there any other way to do this, thanks for any help...

View 1 Replies View Related

Displaying Only Part Of A Field

Jul 23, 2005

On a report I have a field that has a value that can be anywhere from 15 to 25 char. Can I set up the report field to only display the 1st 10 char. ?

jon

View 9 Replies View Related

Search On Part Of A Number

Jan 16, 2006

I have a data base with the field named "PartNumber" this is a 13 diget number. How can I search on the last 7 digets only

View 2 Replies View Related

Searching A Part Of Record..

May 27, 2007

Hello. Im new here and also new to both Visual Basic and Access so please be patient with me :)

I've downloaded SearchDB.zip uploaded by agehoops. Its works exactly like its supposed to but I need to update it a bit.

Im making a MovieDatabase and have a table with Orginaltitle, SwedishTitle, Director, Actors and so on. When I search in "Director" for "Francis Ford Coppola" or just "Francis" the movies which is related to him comes up. But when I search for "Ford Coppola" or only "Coppola" nothing comes up. I want the user to have that possibilty to search using the last part of the name too. Is that possible?

When searching with the binocolor icon it works fine when chosing "part of record", i think thats the english translation for "Del av f�lt". I have a swedish acces version so im not so sure but anyhow I think you understand what I mean.

To sum it up I want the ability to search writing only a part of what the record contain including the last part and not only the first part like it is right now.

Appreciate any help!

Regards,
SoloJuve

View 6 Replies View Related

Excel In Access --- Part 2

Feb 13, 2008

Excel in Access (Part 1) (http://www.access-programmers.co.uk/forums/showthread.php?p=671226)
Excel in Access (Part 3) (http://www.access-programmers.co.uk/forums/showthread.php?t=143970)

Video Version HERE: (http://www.access-programmers.co.uk/forums/showthread.php?t=144045)

How to use the Normalization Form (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)

In Excel in Access (Part 1), we went from this:

http://i185.photobucket.com/albums/x317/UncleGizmo/StudentTableBoolean_2.png

To this:

http://i185.photobucket.com/albums/x317/UncleGizmo/StudentTableTransposed2.png

This was achieved with a form based tool available to DOWNLOAD here. (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804)

See instructions on its use here: (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)

However you may be looking at the new resultant table and wondering what on earth to do with it. I hope to take you through the process of making it into something useful in this thread.

The first thing you will notice is that where the check box is not checked, then that whole row is redundant, for instance there’s no need to record that ID number “1” --- “Has Not” taken Maths, English, Geography, Physics etc, it would suffice just to record the subjects that have been taken , In this case Biology, PT and Social. Looking at those entries in particular, then a general rule of logic can be defined, “delete all the rows where the check boxes are false”.

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1b.png

Once you have deleted all of those rows,

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1c.png

then it becomes obvious that the check boxes themselves which now “All” contain a true value are also redundant, they can be deleted just leaving you the text entry identifying the subject taken by each student.

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1d.png

Using the “relational” properties of the database that is one more thing you can do which will improve efficiency and that is to replace each text entry --- Maths, English, Geography, Physics etc, with a number linking that field to a look up table.

First of all you need to create a look up table; this can be done by applying a create table query to extract just the unique values for the “subject” There is a video showing how to do this here: (At time index 1min) (http://www.viddler.com/explore/TonyHine/videos/67/fullscreen)

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1e.png

This unique list should be called “tblSubject” this table is not quite finished, you need to add an identity column to the left of the text representing the individual subject, this identity will then appear in the previous table.

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1f.png

Once you have completed the “look up table” you then need to replace the entries in the student subject table “tblStudentSubject” where it shows subject in text form with the number representing the link to the look up table. This is the query:

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1g.png

And here is the new column created:

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1h.png

This way your design changes to the table are making it much more efficient, holding the same information but with less data.

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1k.png

View 4 Replies View Related

Excel In Access --- Part 3

Feb 19, 2008

Excel in Access (Part 1) (http://www.access-programmers.co.uk/forums/showthread.php?p=671226)
Excel in Access (Part 2) (http://www.access-programmers.co.uk/forums/showthread.php?t=143607)

Video Version HERE: (http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619%3ABlogPost%3A7031)

How to use the Normalization Form (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)

Using the normalization form --- Download Here (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804) we converted part of a spreadsheet type table into the beginnings of a relational table.

From this table we derived a “look up table” now giving us a total of three tables, the remains of the first table, “tblStudent” (the student names), the next table “tblStudentSubject” stores the subject(s) related to each student, and finally a third table, “tblSubject” a “look-up table” to store the actual subject description.

http://i185.photobucket.com/albums/x317/UncleGizmo/ThreeTables.png

From the table “tblStudentSubject” we created a form in datasheet view:

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm.png

Now all we need do is combine this datasheet view form with a form based on the students list, and this will give us a form for correctly displaying the student names and the subject(S) the student is taking in one Form:

Using the wizard create a basic form from the student table and name it “frmStudent” arrange its size so it has some open space as shown.

http://i185.photobucket.com/albums/x317/UncleGizmo/StudentForm.png

Now open “frmStudent” in design view

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_1.png

And drag the subform “sfrmlStudentSubject” into the clear area on the student form “frmStudent”

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_2.png

You may wish to delete the text box, you don’t have to but I usually find it looks better without it.

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_3.png

Size the form to suit

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_4.png

Now save the form and have a look, you will notice that it incorrectly shows all of the records in the subform,

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_5.png

Now the next bit is tricky, for two reasons, the form isn’t really on top of the other form, it actually sits in a subform window and you need to gain access to the properties of this subform window by clicking on the tiny line that you can just see around your subform. This can be a difficult task to master first time.

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_6.png

Now open the “subform field linker” dialog box by clicking on the ellipsis (…)

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_7.png

Phone: +44 1635 522233
Mobile: +44 7747 018875
Email: email@tonyhine.co.uk

Web: http://msaccesshintsandtips.ning.com

View 1 Replies View Related

Filtering Out Part Of A Field

Apr 7, 2008

Hi,

Apologies, I have no idea if this should go in reports, queries, macros or modules and VBA as I'm a bit stuck but hopefully it's fairly simple.

I've been teaching myself access and it's been going well. What I have now been asked to do is produce a report that generates the shift patterns for everyone in the office as an HTML document. Now, the data is all exported from another program and I've had no problem getting the data into access easily. The problem I have is the format some of the data is in.

The major one that I need to solve is showing what time people are meant to take their lunch break each day. The field for break is filled in in the following format.

07/04/2008 12:45:00

Now, I have the date from elsewhere so I really don't want the date to show up so I need something that removes the date from this field. Is this going to be easy to do? (Ideally I'd like it so that the above example actually just returned 12:45 but if it has the 00 on the end that would not be the end of the world)

Apologies if this is in the wrong part of your forum.

View 3 Replies View Related

Remove Part Of A String

Feb 7, 2005

Hi folks,
I have a table with the words "Church of (whatever)" in one of the records. I am attempting to do an update query to remove the "Church of" part but leave the remaining part of the string. I am using a combination of Format and left but am not having any look. Any suggestion are much appreciated.
Thanks
Colin.

View 2 Replies View Related

Search Part Of Name Field

Jul 23, 2005

Hi Guys!

I'm working on this database in which I have 2 forms:

one form has personal information (PersonalInfo)
and the other one is a search form (Search)

In the search form I have a field in which I want to type any part of the Name field in PersonalInfo form and I want to retrieve all the records that have this part of the name.

Here is what I tried:

In the PersonalInfo form, I want to the criteria of the NameField and typed the following:

Like %[Forms]![Search]![SearchField]%

but it didn't work.

Any help will be very much appreciated.

Regards,
CS.

View 4 Replies View Related

Extracting Part Of A Field

Jul 27, 2005

I found a thread from last year that is close to solving this question but not 100% so I'll post this new thread.

I have a field called ITEM_NUM in a database with the following structure:

XX-YYYYYYYYY
XX-YYYYY
XX-YYYYYYYYYYYY

I need to extract the Y portion of the data and a Query format would be the best.

FYI, the X portion consists of 2 Letters/numbers then the Hyphen and the Y portion is variable in length.

thanks for your help!

View 1 Replies View Related

How To Select Part Of A Field

Sep 6, 2005

I have joined 3 tables using the query design and I have 3 different fileds. There are fields that have information that I don't need. e.g. I don't want the first 10 characters of field 1. How do I do this in query design (even in SQL view). Or do I need to do this in the table itself before joining the table. Hope my question makes sense. Thanks for any input.

View 1 Replies View Related

Iif With True Part Containing 2 Values

Nov 28, 2005

First Post - please be gentle :)

I may be missing something here, but can any one help?

I am using a Iif statment in a query to filter records as follows:

If([value from a form]= "all holidays",("weeks hols" or "days hols"),"not a holiday")

This is returning a to complex to calculate error - I think it is a Syntax error but could be wrong
but if I enter The string "week hols" or "days Hol" as the criteria without the Iif statment that works fine.

Thanks Dean

View 5 Replies View Related

Removing Part Of A String

Nov 29, 2005

I have a bunch of server names that have names like aaa.bbb, xxx.yyy. All i need from these server names is the name before the first "." so in the first example all I would need is aaa. I've been using this formula in excel MID(A1,1,FIND(".",A1) - 1). This works great.. Is there any way to do this in access?

View 1 Replies View Related

Contains Part Of Text Formula

Dec 12, 2005

Hi.

My name is Amanda, and recently I�ve started using Access again for work. I haven�t touched the program in about five years, so I am rusty. In addition, it turns out that I am the only one in my department who�s ever even opened Access. I�ve tried hunting down a manual around here, and it seems to be an ever ending scavenger hunt. I also have not been able to find the answer to my question via web, FAQ, and experimentation, although, I feel it�s a fairly basic question.

So here it goes. I want to return all fields that contain part of a text. For example, I want all fields that contain the word �lux�, regardless of what else the field contains to be returned. So if a field contains �lux. cars� it will be returned in my query.

I�ve tried goggling �Microsoft Access� with �text formulas,� however, all I receive are excel formulas.

Thank you.

View 2 Replies View Related