Computer Support Forum

Concatenate Date and Number field for Append Query

Question: Concatenate Date and Number field for Append Query

Hello,

In Access, I am trying to develop a process for generating a unique client number that follows a very specific formula: XXX - XXXX - X - XXXX. The first group should represent the quarter and year when the client is first introduced (non-standard year: Feb-Jan). The second group should be a unique, sequential ID number with leading zeros (missing numbers won't matter and leading zeros seems impossible, so I'm planning to use a DMax calculation on the form that generates a sequential number). The third number represents the client's initial program phase ID. The final number relates to the grant contract ID.

I have generated the number with the following in a Select query:

UniqueNumber: DatePart("q",DateAdd("m",-1,[MonthStart])) & Format([MonthStart],"yy") & "-" & [SeqNum] & "-" & [InitialPhaseID] & "-" & [ContractID]

The table already has a primary key, but since I am required to generate client numbers in this format, I want the UniqueNumber to be saved and searchable. This field never needs to change once the original record is saved. I've tried an Append query, but can't make it work. I assume it's related to the fact that I'm combining Date and Number fields, not to mention the DatePart function. I consistently get a key violation error. Any thoughts on how I can proceed?

More replies
Relevance 100%
Preferred Solution: Concatenate Date and Number field for Append Query

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Relevance 100.04%

I want to combine a text field and a date field into one field to use as a reference number. IE 'Mil - 05/11'.

I have got a field with the standard short date format 29/05/2011 (that I have to keep) and I have created a second field with the same date but formatted to a "mm/yy". When I concatenate the Operater Ref field "Mil" to the Date Ref field "05/11" I still get the full date format IE Mil - 29/05/2011.
 

Answer:MS Access Concatenate a date and text field

Jerry-

If you are creating the second field in your table, go to design view and change the Data Type to Text then try your concatenation. I built a mini-table and queried it and this worked for me... hope this works for you!
 

19 more replies
Relevance 97.17%

I created an update query and when I run it I get the following message "An INSERT INTO query cannot contain a multi-valued field." I believe that problem is that I have two look up values in the Append to table. How do I solve this problem? How do I use an Assigned.To statement?
 

Answer:An INSERT INTO query cannot contain a multi-value field error message Append Qry

Is this is SQL? Anyway, please copy and paste the query
 

2 more replies
Relevance 90.61%

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Code:
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

Answer:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

16 more replies
Relevance 89.38%

I accidentally formatted the date cell & it only accept numbers

Answer:how to correct a date field when you enter a date a number a

I don't know what you mean by "it only accepts numbers".Why can't you just format it as a date again?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 84.87%

Can someone help please?

I'm setting up a query in Excel from an SQL database. How do I (or what do I) do to ensure only todays data is shown - I suppose I'm looking for an"=TODAY" type thing but don't know enough to be able to do it.

Thanks in anticipation.

Moll )
 

Answer:Excel query on Date field

it can depend on how the date system is set up but the following page has pretty much every SQL date function going

http://www.connx.com/products/connx/Connx%208.5%20UserGuide/sql_date.htm
 

1 more replies
Relevance 83.64%

Greetings

It's been a long time since I last posted here, but hopefully all you wonderful people will be able to help...

Here is my problem:

A customer of mine has a SQL database where one of the fields is a date/time field that looks like this:

13/02/2003 14:32:58
14/02/2003 08:59:10
15/02/2003 17:09:37

Now, I need to delete all the records for a particular day (One of their HD's failed, and they had to restore the data and lost a day, but the entries are still in the database, and can not be re-entered until the old entries are deleted).

How do I structure a query to delete all the records for a particular day? Preferably, I would like a SELECT statement first, just to check that the records found correspond to the delete criteria, and then the DELETE statement.

Thanks

Reuel Miller

The Ex-NT moderator on this board

Every morning is the dawn of a new error
 

Answer:SQL Query help required with date/time field

Greetings

Not to worry chaps, I figured out the answer all by my lonesome

the statements I used were:

select * from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'

delete from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'
cheers

Reuel Miller
 

1 more replies
Relevance 83.64%

I am trying to add a sequential number to a date field in an Access form.
The format of the field is PS 18 Jan 11 xx, the PS is fixed, the current date and the xx is my sequential number I want to add.

Is there a way, to do this when the date is entered? And I would need the sequential number to reset to 01 with a new day.

I'm new to this, any help would be great!

Thanks!
 

Answer:Help in appending a sequential number to a date field

16 more replies
Relevance 82.82%

I have a linked Excel spreadsheet to show invoices received and I insert a date when they are paid. I cannot figure out the expression to use to show all records where no date has been inserted. Can anyone help please.Thankyou

More replies
Relevance 82%

Access 2007 Update Query For Updating A Date Field

Hi I have been asked to write a query for an Access 2007 db

I need to update a date field in a table based on another date field in the same table

1st date field is the person's start date (StartDate)
2nd date field is the person's project date (ProjectDate)
Without going into too much detail, the Project Start date always begins on the 1st of the month

If the person's StartDate is between the 1st - 14th the project start date is counted as on the first day of the StartDate month/year

If the person's StartDate is between the 15th - end of month the project start date is counted on the 1st day of the NEXT month

Example:
StartDate = 7/1/2011
ProjectDate = 7/1/2011

StartDate = 7/14/2011
ProjectDate = 7/1/2011

StartDate = 7/15/2011
ProjectDate = 8/1/2011

StartDate = 7/30/2011
ProjectDate = 8/1/2011

I would like to update the ProjectDate in an update query

Any suggestions? Thanks
 

Answer:Access 2007 Update Query For Updating A Date Field

Create a Column heading like this
numdays: format([StartDate], "dd")
or
numdays: datepart("dd", [StartDate])
this will give you the day of the month on it's own
add a second Column
NewProjdate: IIf([numdays]>14,Format([StartDate],"mm")+1 & "/01/" & Format([StartDate],"yyyy"),Format([StartDate],"mm") & "/01/" & Format([StartDate],"yyyy"))

This will give you the date to use in the update query.
 

3 more replies
Relevance 79.95%

I've inherited a Access Database that was started over 16 years ago. For some reason, when we try to move items from the active table to the archived table, anything entered after 2011 will not move over. I've looked over the query and both tables, and nothing jumps out at me about a limitation and I've viewed the records from 2011 and compared them to 2012, but there isn't anything different that I can see. I also checked for required fields in the archive table. I can't find anything that would prevent a record from appending to the archive table.

Has anyone else had an issue like this? I just noticed the issue a few weeks ago, so I haven't run the query yet, but nothing with a entry date after 2011 has transferred, while anything before that date has continued to transfer for the past 4 years.
 

Answer:Append query won't append some records

Normally when you run an "Action" query it gives you a message summarising how many records it is appending and how many it has not and why.
Do you get that message?
The usual problems with appending data are Duplicates where No Duplicates are allowed and not meeting the requirements of a particular field. ie text instead of a Number etc.
 

3 more replies
Relevance 73.8%

Ok, my problem is...i have a table with 2 field diferent name

Example
ID 1
unit_price 238.56
unit_pricelast ......

It is possible to update "unit_pricelast" with value of "unit_price" but using same ID?

Result:
ID 1
unit_price 238.56 (later i must change value of this field cuz i have items with last month price and price with actual)
unit_pricelast 238.56

ty for fast respond
Goran
 

More replies
Relevance 72.98%

Hello,

Using Office XP Im trying to create a query that will ask for a message, then append this message to the end of a field on records where a 2nd field (a tick box) = Yes.

The code at the moment looks like this...
Code:

UPDATE [TABLE] SET [TABLE].[FIELD] = [FIELD]+". "+[:Enter Message]
WHERE [TABLE].TICKBOX=Yes;

This seems to work apart from where FIELD is null, on these occasions the FIELD remains blank.
TIA
Slimboy Fat
 

Answer:Solved: Append Message To Field In Access

15 more replies
Relevance 72.57%

Am sort of new to the world of Access and VBA and am working on a DB. I am trying to take the selection from a combo box in one field and combine that to a "000X" number to create an incident # based on that selection that would populate in a field that is already set up in the same table. This number would increased as new incidents are entered into the DB.

An example would be if "Hang up" were chosen from the combo box the incident # field would then show Hang Up-0001 and increase the next time Hang up was chosen. Same thing if "Theft" was chosen etc.

Is something like this possible?

Thanks
Erica
 

Answer:Access 2007 VBA code to concatenate result from one field to incremental #

You may be looking at this in the wrong way. I would create a customer table or however you want to refer to them and a seperate incident table that will hold all of the information related to a given incident. You could also create a seperate table related to the incident table for troubleshooting steps or other items related to the given incident. The concatenation would probably be unnecessary if you lay out your tables correctly.
 

1 more replies
Relevance 71.34%

My PC configration is MS Window 7 Professional (64-bit Traditional Chinese version), MS Office 2010 (32-bit Traditional Chinese version) and IBM Client Access V6R1M0 (64-bit English version).

The problem is that the actual numeric value is 1,2 or 3 only in the physical file. After using the Data Transfer Excel Add-in function to download that physical file via a dtf file, the numeric value is changed as 10, 20 or 30 in the Excel.

However, this problem is occurred randomly, i.e., sometimes happen incorrect value, sometimes is normal. Any idea? Many thanks!
 

More replies
Relevance 70.52%

I use due dates on my Outlook tasks. When I customize my Tasks list (in the Calendar view) to group by due date, I just get too many groupings (one for each due date!). I would like to know if I can create a custom field called 'Due Month' and/or 'Due Week', extracted from the 'Due Date', so I can view my tasks by what is due during a given week or month. If the answer is yes, any suggestions on the technique I would use would be greatly appreciated.
Thanks in advance,
Mac
 

More replies
Relevance 69.7%

I need a date field changed into a text field of YYYYMMDD so for example the date of birth field is 3/17/1953 and I need that converted to 19530317 in the Excel spreadsheet. Thanks.

Answer:I need a date field changed into a text field of YYYYMMDD

under the format options.

5 more replies
Relevance 69.29%

Hi guys, hope someone can help with this, though I'm not sure if it's really possible.

I've got hundreds of Excel sheets which all use the same variable (column header) names, except that they all have different numbers of variables. For example one sheet might have:

S_1 | S_11 | S_111 | S_112 | S_12 | S_121 | S_122 | S_2... etc

Whereas another sheet might just have:

S_1 | S_11 | S_12 | S_2

So if you can imagine, hundreds of sheets which all use the same coding for the variables, but each of which reporting different numbers of variables.

I want to get all of the sheets into a single sheet, the eventual goal is to convert it to just one Stata file, but the fact that the sheets are not of a consistent format is a real problem.

What I really need is for Excel to recognise the header names and move data according to those, rather than according to which cells data occupy. Is there some kind of macro which can do this? Or am I just going to have to do it manually? (which will take all year!)
 

Answer:concatenate worksheets with different number of variables

16 more replies
Relevance 68.88%

I need to automatically add some records to a table. It's a simple table with a transaction ID (auto-number), account, store, and distribution amount. I enter all the distributions (expenses) on a form. I need to enter a total of all the expense distributions as a credit, and a final line with account = "End Transaction". It has to be this way, and all on one table so I can create and export a text file to upload to another program.

I seem to be doing OK with everything except the total. I've built an Append Query, which sums all the expense distributions (which are debits). The problem is that I need to "subtract" the total (as a credit) so that all the debits and credits net to zero. How do you get the total to be a reverse sum of the debits?

Any ideas????
 

Answer:Access Append Query

Originally posted by JudyB:
I need to automatically add some records to a table. It's a simple table with a transaction ID (auto-number), account, store, and distribution amount. I enter all the distributions (expenses) on a form. I need to enter a total of all the expense distributions as a credit, and a final line with account = "End Transaction". It has to be this way, and all on one table so I can create and export a text file to upload to another program.

I seem to be doing OK with everything except the total. I've built an Append Query, which sums all the expense distributions (which are debits). The problem is that I need to "subtract" the total (as a credit) so that all the debits and credits net to zero. How do you get the total to be a reverse sum of the debits?

Any ideas???? Click to expand...

hi Judy....

I'll try to help you.... but your post is confusing the [email protected] out of me

I highlighted the areas that need further explanation (for me.... although maybe other people might have understood it )....
so if you can explain those areas a bit better... maybe I can visualize a solution for you.

thanks
khaki
 

3 more replies
Relevance 68.88%

I'm trying to create an Append Query that will do the following:

Table One is set up as follows:

Fname, Lname, Email, Rfname, Rlname, Remail

Rfname, Lname, Remail are fields a person can fill out if they wish to refer a colleague to a maillist.

Table two (the table to append to) is as follows:

Fname, Lname, Email

I would like to create a query that appends Rfname, Rlname, and Remail to Fname, Lname, and Email.

When I set up a query to do this, asking Email and Remail in table one to be appended to field Email in table two, I receive the error message: "Duplicate output destination Email."

I'm wondering if there is a way to design a fairly simple query to accomplish this without creating a query that writes out Rfname, Rlname, and Remail to a separate file, appending this file to table two, etc.

Any ideas/suggestions welcome, or advice on where you can learn more about advanced Access queries welcome!

Thanks in advance.

rick
 

Answer:Access Append Query

6 more replies
Relevance 68.47%

I am trying to append some GPS location data (435322 records) into my main database of telemetry locations (14335 records) using Microsoft ACCESS. I have unique ID's for each animal (total of 218) in my study that are the same for both databases and they have many locations (UTM's) per individual (4000+). When I check the query in table view after building it, I get the added database only. I suspect it has something to do with duplicates. I want to add individual locations for animals linking by ID's and it does not work. Any Ideas?
 

Answer:Access append query not working

I assume that you are trying to append the data to a Table that is separate from the maon Animal table?
If so check the Key indexed field and also check the relationship with the main table.
 

1 more replies
Relevance 68.47%

Hi,

I have an append query that pulls data from 2 tables joined by the "CustNum" If the order has a seperate delivery address the program saves the the delivery address in the Customers table so then i need to join the tables by "CustNum" in the customers table and the "ShipNum" in the orders table. I was thinking is it possible to use an IIF statement?

for example if the "ShipNum" = 0 then the join would be "CustNum" in the customers table and "CustNum" in the orders table and if the "ShipNum" is greater than 0 the join would be "CustNum" in the customers table and "ShipNum" in the orders table.

Ihope this makes sense and someone can help me.

Thanks.
 

Answer:Help With Append Query Ms Access 2003

7 more replies
Relevance 68.47%

Hi all,

Using Access, mdb is in Access 2000 format, using in Access 2007. I have a query with the following SQL:

Code:
INSERT INTO tblTrainingTopics ( Topic )
SELECT [Forms]![frmTrainingTopics]![txbTopic] AS Expr_Add
FROM tblTrainingTopics;
I want to re-use this query, just change the string of data being appended to the specified table. So in order for me to re-use this I would need to replace the [Forms]![frmTrainingTopics]![txbTopic] part. Basically, this is on one form (the frmTrainingTopics obviously) and i want to use it on another form. If I cannot do this, should I just create two append queries?

Thanks!

Edit: Didn't clearly state my request, re-iterated.
 

Answer:Access: Append query, user value

Zack, you can just change the query "on the fly" using a VBA SQL statement, but personally I prefer to create a new query which can be tested independently of the form that you are going to use it on.
 

2 more replies
Relevance 67.65%

I have an unboud list box. I want to set it up so the user can select multiple items in the list, then click a button to have the selected items appended to a table. I've tried several things (see below) and it isn't working. Thank you in advance for any help you may have.

SQL examples of what I've tried.

"INSERT INTO Lead_Emp_Join ( LeadID )
SELECT [Forms]![Assign_Leads]![NewLeads] AS LeadID;
"

"INSERT INTO Lead_Emp_Join ( LeadID )
SELECT Leads_All.LeadID
FROM Leads_All
WHERE (((Leads_All.LeadID)=[Forms]![Assign_Leads]![NewLeads]));"
 

More replies
Relevance 67.65%

Im using the select query to copy a table to another and in the process Id like to update data, but cannot figure out how. In design mode, Ive have, for the column [ProductCode], containing Axx, Bxx, Cxx and Dxx where xx are numbers from 00 to 99.

My query criteria is

Code:

[FONT=Calibri][SIZE=3]IIf(Like "D*" And <>"D02" And <>"D08" And <>"D35",[ProductCode]="DDD",[ProductCode])[/SIZE][/FONT]
Which returns [ProductCodes]. Axx to Cxx plus D02,D08 and D35. Not olnly is DDD missing, all the the Dxx are not there as well. Can anyone give me a pointer?
 

Answer:Solved: Append/Slect Query – Access

I am not certain that the syntax or order is correct.
I would try building it a bit at a time, starting with
<> "D02" And <> "D08" And <>"D35"
to see if that excludes those values.

It would help if you could post a Table with some of those product codes in for me to test.
 

2 more replies
Relevance 67.65%

I have a temp working in a copy of a database that is in a secure location that she does not have permission to access. I have exported the entries she did into Excel with the intention of appending them to the actual database using an append query. I have imported the spreadsheet as a table and have been trying to append the records to the main table but nothing is happening when I run the query.
NOTE: I am getting an error message that states: "An INSERT INTO query cannot contain a multi-valued field "
 

Answer:Append Query trouble in Access 2007

Margo, I would ensure that all the fields in the Import table are checked and ensure that they match the Field types that you are trying to append to.
 

2 more replies
Relevance 67.65%

I have linked a contact folder from Outlook to my database. I have already written a make table query that has been the fields I want to work with into another table. I want to write a query that will update this table with any new entries in the outlook folder.

It has been a while since I worked with Access and not sure what criteria to set to only add the new data.

I appreciate the help.
 

Answer:Access 2003 - Append or Update Query

UTC, welcome to the Forum.
For appending new data I would add a "control" field which adds together fields like the contacts name and birth date or Social security number to create a Unique identity. Set that field to "Indexed - No Duplicates".
Also have this concatenation field in the Append Query, only new records can be added to the table.
 

1 more replies
Relevance 67.65%

I'm trying to create an append query in Access, the problem is everytime I run the query, it says there are no records to append.

Field Names:

Last Name
First Name
Street Address
City
State
Zip Code
SSN(primary key)
Branch
Term Date
Letter 1 Sent
Letter 2 Sent
Letter 3 Sent
Date Received = criteria is, "Is Not Null"
Uniforms Returned = criteria is, "Yes"

The same field names are both tables.
 

Answer:Solved: MS Access append query question

Did you run the query as a select query and checked to see if it returned values first? IS Not Null should not be in quotes as well. Quotes makes it think that it is looking for text. You will see it will capitalize it when it recognizes it as an operator when you tab out of the criteria section of the field.
 

2 more replies
Relevance 67.65%

I am trying to use an append query to add to a table named Fees. My query is as follows:
INSERT INTO Fees ( ID, TotalBill, [DATE] )
SELECT READINGS.ID (findloc([meterbk],[reading],[xreading]))+0 AS TotalBill,
READINGS.DATE
FROM READINGS INNER JOIN ACCOUNTS ON
READINGS.ParcelID=ACCOUNTS.ParcelId
WHERE (((READINGS.DATE)>=Forms![Process Rates]!BegDate And
(READINGS.DATE)<=Forms![Process Rates]!EndDate));

When I run the query, no records show in the table. This is my first time posting, so I don't know exactly what you need for information.

Module 1

Public Function FindLoc(MeterBK, reading, xreading)
Dim FEE as Currency
Select Case Meter BK
'Rutland Town Books
Case "901"
If reading <> vbEmpty And xreading <> vb Empty Then
Let FEE = ((((reading - xreading) / 100) * [Forms]![Process Rates]![TwnRate])
+[Forms]![Process Rates]![TwnMeter])
End if

This query deletes the old rates

DELETE Fees.ID, Fees, TotalBill, Fees.DATE
FROM Fees
WHERE (((Fees.Date)>=[Forms]![Process Rates]![BegDate] And
(Fees.DATE)<=[Forms]![Process Rates]![EndDate]));

Any help is appreciated.
 

Answer:Access 2003 Append Query Problem

9 more replies
Relevance 67.65%

I rebuilt my system and lost one functionality. When I copy a file, say a "Text.txt", using Explorer to the same folder, I get a new file named "Text - Copy.txt." Before I rebuilt, I would get "Text_2.txt." If I pasted again, I used to get "Text_3.txt."

I checked another Windows 7 system and it works like mine used to, i.e. appends a version number to the filename when copied to same folder instead of appending " - copy."

System: HP Compaq 6910P laptop
Memory: 2GB
CPU: Intel T8300
OS: Windows 7 Professional 32 bit
All drivers and enhancements loaded from HP's site (same as before)
Member of domain, I have local admin privileges but not domain admin and some settings are controlled via the domain admin.

The other system that works like mine used is a:
System: Toshiba L505D-S694
Memory: 2GB
OS: Toshiba Windows 7 Home Premium 32 (upgrade from Vista 32)
Non-domain machine

Any ideas how to change this setting to get my version numbers back?

Answer:Filename append with number on copy to same folder?

One quick thing to check.. hit Windows-Pause hotkey
Click on System Protection
Make sure it says On for your system drive

The previous versions of files uses Shadow Copy/System Restore

9 more replies
Relevance 67.24%

Hi,

I am creating a few macros in LO Calc, the last step is to export data to a text file adding the current date and time to the file name.

Ive googled a few hours but couldnt find a way to do it.

I d appreciate if anyone could point me to a good resource of such information. (the LO wiki wasnt helpful)
The code would be great, but I also plan to add more features such as deleting row depending on values, so learning how to do it myself would save the forum from having me posting several help requests

Thanks in advance.
HQ
 

Answer:LibreOffice macro, append date to filename

I've only just moved to LibreOffice myself, and haven't had occasion to do anything with macros as yet.
One suggestion if you're looking to teach yourself, the code base should still be quite similar to the OpenOffice it was forked from. You may be able to get tutorials for writing macros under the older platform more easily.
 

1 more replies
Relevance 67.24%

I am trying to use a .bat file that can be scheduled to move many files from a folder to another folder on a different server and rename the file by appending the system's date to the each file in the destination folder. I need assistance.Joshua

Answer:Move file and append System date

Josh, You'll have better luck in Programming forum.=====================================Helping others achieve escape felicityM2

2 more replies
Relevance 66.83%

Hi Guys!

First post and in need of help

I have a form (frmAllCustomers) based on the table (tblAllCustomers) that displays all companies in the market with potential business for my company. When a new contract is undertaken i have a command button on frmAllCustomers that runs an append query to move the company's details into frmClients (based on tblClients).
I want to add to my code so that if the record trying to be appended from frmAllCustomers already exists in frmClients, a friendly message box appears such as "Company is already a customer!" instead of Access' Key Violation message. I've been told DLookup is the way to go. any ideas? I have something similar to this:


DLookup(["CustomerID"]["tblClient"] me.customerID)
If DLookup is = 0 Then
Run query
Else
MsgBox(Company already a customer!)

Many Thanx in advance

Andy
 

Answer:MS Access: Avoiding duplicates from an append query using DLookup?

abue42 said:



Hi Guys!

First post and in need of help

I have a form (frmAllCustomers) based on the table (tblAllCustomers) that displays all companies in the market with potential business for my company. When a new contract is undertaken i have a command button on frmAllCustomers that runs an append query to move the company's details into frmClients (based on tblClients).
I want to add to my code so that if the record trying to be appended from frmAllCustomers already exists in frmClients, a friendly message box appears such as "Company is already a customer!" instead of Access' Key Violation message. I've been told DLookup is the way to go. any ideas? I have something similar to this:


DLookup(["CustomerID"]["tblClient"] me.customerID)
If DLookup is = 0 Then
Run query
Else
MsgBox(Company already a customer!)

Many Thanx in advance

AndyClick to expand...

Hi Andy,

The DLookUp is a possibility.
But I prefer VBA

Example for Access 97 and UP. You must first make a reference tor the Microsoft DAO 3.6 Lib form within the VBA editor
Private Sub cmdDAO_Click()

Dim rsLookUp As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rsLookUp = db.OpenRecordset("SELECT * FROM tabel1 WHERE code = '" & Me.txtLookUp.Value & "'")

If Not rsLookUp.BOF And Not rsLookUp.EOF Then
MsgBox "Record gevonden: "
... Read more

6 more replies
Relevance 66.83%

Bottom line: I'm using a form to input information. Then I made an append query to pull the information from the form and put it into a history table to store the information. Append query runs and has no errors but is only appending 0 rows...

I feel like this is an incredibly simple problem. I've made past append queries run off info from combo boxes but am at a loss as to what's wrong this time and am so frustrated I want to pull my hair out

Specifics: I have a form where I am using combo boxes to input information to schedule a manager's interview availability (Mgr_ID, Date, StartTime, EndTime). I have an empty table waiting to be populated with the the manager's availability (columns titled same as corresponding combo boxes). Created append query using builder to formulate the criteria (Forms!MgrAvail_Form!column name here). At first, my destination table had an autonumber for the primary key. I worried that because I wasn't specifically inputting anything in it via the append query, that I should change it. So I deleted the autonumber and made the four columns into primary keys. I realize this could create db design issues but this point I don't know what else to do...

**I am not using SQL or VBA to run the append query, just the usual QBE**


please. Please. PLEASE! Help! and many thanks in advance!!
 

Answer:Access 2007 - append query only appending 0 rows???

welcome to PCR

you may get a better response by posting in ...

https://www.pcreview.co.uk/forums/forum-250.php

... sorry I can't help



 

5 more replies
Relevance 66.42%

I have a script I have modified to print all unread pdf files in the inbox and mark them as read. Now I would like to add a date and timestamp to the attachments that print out. So the paper copy says the date/time it was received. Or it could say the filename on the printout since I have it adding the date/time to the filename when saving. Is there a way to add this to the printout?

Here is the code I have so far:
Code:
Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Function printFile(pdfName As String)
ShellExecute 0, "Print", pdfName, vbNullString, "", 1
End Function

Public Sub PrintAttachments()
Dim myInbox As MAPIFolder
Dim mailItem As mailItem
Dim attchmt As Attachment
Dim myItem As Outlook.mailItem
Dim pdfName As String
Set myInbox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
For Each mailItem In myInbox.Items
For Each attchmt In mailItem.Attachments
If mailItem.UnRead = True Then
If (InStr(1, attchmt, ".pdf", vbTextCompare) <> 0) Then
pdfName = "C:\Temp\" & Format(mailItem.ReceivedTime, "mm-dd-yyyy_hh-nn-ss") & "-" & Right(... Read more

Answer:VBA append a pdf file with date/time it was received in Outlook

I setup a watermark through my printer to add the date/time to everything that is printed. Is their a way to make it specific to just pdf files through vba code in Outlook?
 

1 more replies
Relevance 66.42%

Hello,

I have a large report of application forms which have been submitted including the date when they were submitted however the dates are formatted incorrectly.

1. Although the cell formatting is dd/mm/yyyy the data is being shown in mm/dd/yyyy which means the dates are incorrect (e.g. 2nd January would read as 1st February.)
2. As a result of this dates which are passed the 12th of a month are not accepted as dates (it reads the day figure as months and there is no 13th month etc)

I have tried to use the text to columns function but that makes no difference and I have tried to extract just the dates but I've had no luck.

Can anyone help, I've attached a small sample of the data.

Thanks

Daniel
 

Answer:Exported date field not recognized as date in Excel

Where does the data come from?
What do you get if you have no cell format?
 

3 more replies
Relevance 66.01%

I have a table that has a field with multiple values separated by commas. I want to parse out these values and create multiple records out of this one record:

COMPANY PRODUCT_SERVICES
ABC 125,20,94
DEF 04,88
GHI 22,96

I want to create this:

COMPANY PRODUCT_SERVICES
ABC 125
ABC 20
ABC 94
DEF 04
DEF 88
GHI 22
GHI 96

I then will be able to match these product/services codes to descriptions to create a directory based on those products and services, listing each company underneath the actual product/services description.

My knowledge of VBA is limited...

Help!


Lori
 

Answer:"Un"concatenate a field to create multiple records in MS Access

Did I stump the band??
 

7 more replies
Relevance 66.01%

I have multiple office locations that don't have a network or computer expertise but they do have email. I needed to get them to enter their budget data, export it and then email it to a central office where each department's export to excel will be imported into the master database to be worked with further there. I didn't want the employees to have too many difficult operations so I set up a query to combine all their tables into one query and then one button click exports it to an email to a guy at the central office. The problem is that once he gets these emails in, I want him to be able to append each of their spreadsheet data back onto the three tables that they were originally on. He will have to do this for each department that sends him info. The problem is the append query only works for the first table. I have set him up with imports into an IMPORT DATA table. Once there, the append queries are to query out just the data to append to the PROJECT table, then another append query is to get the data for the COST table and the third is for the FUNDING table.

Only the Project table which is the lead table in all the relationships will run this append query. The other two keep losing the data in the query. Everytime I reopen the query I see that the fields I had put on them are gone and the query is blank again. I am getting this message "Reserved error(-3087); there is no message for this error".

Can you point me in the right direction?

Marg... Read more

Answer:Solved: Access 2010 append query error message

6 more replies
Relevance 66.01%

I wrote an append query to add new data from one table to another table, but when I ran the query, it had duplicated each record ten times.

The table I am appending to is the main table. It has five more fields than the append from table. It has an auto number which is also the primary key. I did not include the auto number field in the append query, as advised by the help file. The tables have no relationship.

Not only did it post 430 lines instead of 43, the autonumber started at #397 instead of row #10 which was the next row. No rows have been deleted from the table as I just created it, so it's not that. This error may correct itself if I can correct the other error, but I thought it was worth mentioning.

Can anyone give any pointers to where I am going wrong?

Many thanks!
 

Answer:Solved: Access 2003 append query duping records

7 more replies
Relevance 65.6%

Hi there,

I have a table with the information related with the kms of our fleet company. The table as the kms at the end of the month, and i didn't put the kms at the begining of the month because i always thought it was redundant info.

Now i need to query the DB to get (kmsf - kmsi). I have tried to create crosstables and i managed to resolve the problem but always get another problem: the fields in the query of a crosstable are dinamic and i don't get always the same months and this isn't what i really want.

Is there a way to make a query that returns the value of kms_at_the_end_of_month and the kms_at_the_end_of_the last_month?

Thanks!
 

More replies
Relevance 65.6%

Hi there,

Once again I need your help in Access. I'm trying to do a calculated field in a query that based on a query. For example, query B is based on query A. I want to add a calculated field called Totals in query B. The field totals does not exist in any of my tables.

Here is an example.

Totals: [Field1]+[Field2]+[Field3]

In the totals line I clicked on Expression. But the totals do not appear when I view my query. I get no errors on the formula. What at I doing wrong??

Mario
 

Answer:Calculated field in a query based on a query

7 more replies
Relevance 65.19%

Hi there,

I have obtained an Excel 2010 workbook containing 1339 sheets of tables. The tables are all in the exact same format and represent an itinerary. Each sheet was extracted from a different pdf page and each entry is unique. There are 34 row entries on each sheet and each entry has 9 columns.

I need to be able to combine all 1339 tables into one master table on one sheet so I can sort and filter as one.

Many posts have discussed the Consolidate function however this involves naming ranges for each sheet. Obviously I can't do this easily for 1339 sheets.

Is there a way I can get all the data appended or concatenated into one master sheet? I have tried importing to Access 2010 to help but it only appears to allow one sheet at a time.

I feel like this should be easy so apologize if it is in fact.

Thanks
 

Answer:Append large number of tables on Excel sheets to one master table

6 more replies
Relevance 65.19%

Help! This should be easy but I can't get it to work.

I have a report that must group by Trip# and sort by ascending activity date all of the various activities that took place on multiple days of the trip.
Further, the trip with the earliest date must come first regardless of the Trip#.
I've accomplished getting the earliest starting date trip first and grouping its detail with a unique string made up of the earliest date of each trip formatted to YYYYMMDD+Trip#. This prints a trip total and works fine.

But the daily activity for the trip is out of date sequence.
Adding a Date field to the 'Sorting and Grouping' insists on breaking on and printing a sub-total for each of the trips activity dates.
Without the sorting & grouping by date the trip records are out of sequence even though the data source query is sorted ascending by activity date.

(see attachments that show the above)

The only solution I can think of is to create a sub-report sorted by activity date.

Does anyone have any better ideas?

Jim Shannon
JES Computer Systems
Access Developer.
 

Answer:How sort by 2nd field (date) without grouping when date changes?

7 more replies
Relevance 62.73%

I have a query where a number field is multiplied by a currency field. The expression is numerically correct but is not expressed in currency format. How do I change that?
 

Answer:number field multiplied by currency field express in currency

8 more replies
Relevance 61.91%

I need to return a number associated with a date based on today's date. For example: A1:A24 are numbers 1 to 24 and B1:B24 contain the 15th and 30th for each month of the year; 15-Jan, 30-Jan, 15-Feb, 28-Feb, ... 30-Dec.Cell C1 needs to return the number from A1:A24 based upon today's date compared to the date in B1:B24.For example today is 08/21/15, what number should be returned in Cell C1. It should be 15 as 08/21 is less than 30-Aug.message edited by jmichels

Answer:Return a number based on today's date compared another date.

Try this formula, see how it work for you:=INDEX(A1:B24,MATCH(TODAY(),B1:B24,1),1)This will work for most dates, but errors out if the Date is 01/14/15 or less.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

4 more replies
Relevance 61.5%

Hello, I am having trouble finding the previous 2 days totals in a query. I have a totals query with the following fields:
JobID - Criteria: Between 500 And 599
WorkType
JulianDate
Batch
TotalProcessed - calculated field, grouped on Sum. sums field entry
ProdDate - Short Date - Grouped on Where - Criteria Date()-1 Or Date()-2

Today's Julian Date is 199 so query should give me Julian Dates 197 & 198, but instead returns Julian Dates 188 - 196.

Thank you for your help!
 

Answer:Solved: AC2007 Find Date()-1 And Date()-2 in Query

The criteria that you should use for a date period is
Between Date()-1 and Date()-2
 

2 more replies
Relevance 59.45%

what is that supposed to mean?
im trying to make an append query but this message comes up
 

Answer:query must have at least one destination field?

6 more replies
Relevance 59.45%

I've encountered one of the strangest things I've ever seen in Access. Wrote a query that supports a mail-merge letter. One of the fields in the query is an expression named "Expires". The formula is Month([DateJoined]) & "-" & Day([DateJoined]) & "-" & Year(Date())+1. What it produces (example) is 4-11-2003... on MY computer. But on (an old) computer in a client's office where it needs to be used, the field is always truncated at 6 characters; i.e., the above date shows only as "4-11-2". If the day is between 1 and 9, it would show "4-9-20".

I cannot find a setting or configuration for Access or Windows that controls the length of text expressions, and no other expressions behave this way. It is unaffected by changes in the Windows Regional Settings.

Why is this one field truncating only on one computer? Where's the setting to keep it from doing this?
 

Answer:Query field truncates

11 more replies
Relevance 59.45%

I am working in Access 2003 and I have an unbound Field on my main form. How can I get the results of that unbound field into a query?
 

More replies
Relevance 59.45%

Hi, not specifically a Windows 8 question but just a Google.com one..

Why is it that whenever I start typing a search query in the Google box at Google.com, it starts typing into the URL bar rather than the text box in the middle of the screen? This happens whether I use Chrome or Firefox..with Chrome it goes into the URL bar, and with Firefox it goes into a dynamic search box top left.

Just wondering if this is normal? I find it a bit annoying...but maybe thats how things are now?

Answer:Why does my Google query appear in URL field?

Hey mate.

Not sure what you mean entirely? you are able to type queries into the address bar, and it will use your search provider (often google) just like if you were to go to google.com and type it inside the webpage. Works both ways, and this way you don't have to waste time searching for google first.

6 more replies
Relevance 59.04%

Hi,I am creating a database which will include 3 tables:-A contact databaseAn individual members database - with membership numbers starting at MEM 00001An corprorate members database - with membership numbers starting at ORG 00001I only know how to do it just with numbers. Is it straightforward to create an autonumber field in this format ?Thanks for your help.Regards,Julia Evans(Moderator edit: Added Topic Description. jgw)

Answer:Membership number field

I forgot to mention that I am using Access 2010 - and consider myself an "intermediate" level of Access knowledge and experience.

1 more replies
Relevance 59.04%

I work with MS Access 2003.

I am creating a query to show all contracts set up in a certain year that have been cancelled in that specific year. This "Cancelled" field is not a yes/no field, but includes the date on which it has been cancelled (always starting 31/12/YYYY).

First, to show me the contracts set up in a certain year (say 2008) , i created a parameter under the "Contract Year" field, asking which year you want to see (in this case 2008).

I wish to add a criteria that then shows cancelled contracts at the end of that specific year, so 31/12/2008. So whether it is "2008" or another year is dependent on the value in the "Contract Year" field.

I tried the following as criteria for the Column "Cancelled":

#31/12/[Contract Year]#

to try and have it take the year mentioned in that field.
But it doesn't work. Access sees is as a second parameter, instead of a field name.
I tried adding LIKE to it and the &-sign, still no luck.

Is it possible to use a field name in a date criteria and if so, how could i do it?

Thank you very much in advance for your help.
 

More replies
Relevance 59.04%
Question: Update date field

Thanks in advance for your help!

I used to know this and I did a search and couldn't find the answer.

How do you update a field to a date either 6 months away or 12 months away for the current date in that field?

Thanks again!
 

Answer:Update date field

6 more replies
Relevance 59.04%

Not sure how to go about this. I am not a programmer by any means but need to make a program.

I have a text file that is in what is called BAI format (bank standard format to send info). Basically this is a CSV file with a header. Right now I use a mail merge file to update the date field. I am looking to automate this so I would only need one "dummy" file and have a program that will update the date field automatically. The file is opened with notepad. I have done some VB but not a lot. I am looking for the best way to go about doing this. I tried to get some of the programmers to do it but no one wants to worry about supporting it, so I am taking a crack at it.

All help is very much appreciated. One idea I had is maybe a macro in excel but not sure if it would mess up the formating. I will try to get a copy of the file I use and post it.
 

Answer:Updating date field

6 more replies
Relevance 59.04%

Hi,I'm trying to create an if formula and I'm a bit stuck.Column L contains dates separated by commas. I'm trying to create a formula to say:if L2 contains (fixed date from a single cell) then "Exclude" else "No"There are two issues. How do I make the if statement contains not just equals? How do I copy the cells down and keep the field with date in fixed?For example I added the date I needed to 'Delivery sheets d1':=IF(Data!L2='Delivery sheets'!D1,"exclude","No") but when I try and fill down the D1 continues in a series e.g. d1, d2, d3 etcI hope this makes sense. Thanks in advance!

More replies
Relevance 59.04%

Is it possible with Access 2010 to have a date field that would change to current date whenever another particular field is modified. For example, we have comment field and we would like to have another date field that would change to the current date when the comment field has a change.
 

Answer:field modified date

Yes you can do it with simple VBA code, you could also add it to the comment itself, so it appears at the beginning of the comment.
 

1 more replies
Relevance 58.63%

i am creating a DB for all the software installed on PC's i have a table that if a certain software is installed i would enter the version number, and if it is not installed then i would just leave it blank. How can i create a query that will disregard all the null fields (meaning that software is not installed on that specific pc) and jsut show the records that have a version number in it. I am almost positive there is a setting for this, i just can't find it, don't think you have to put some kind of code behind this to create the query. Any help would be much appreciated.
 

Answer:Solved: null field in query

14 more replies
Relevance 58.63%

Please help. I'm running Access 2007. I have a Form that's running off a query. The query has a parameter that requests two user inputs. This works fine except when I open my Form it automatically asks for those two inputs. It still works but I'd like to be able to open the form, enter data and when I get to those fields have the parameter questions populate at that time. Any help is greatly appreciated
 

Answer:Query Parameter Field in a Form

7 more replies
Relevance 58.63%

Long time no see, took a bit of a break... time sure fly by huh?

My Query's Y/N collum result in -1/0 when I run the query. I want it to be Yes/No.
Is there a ... better/quicker way to do this other than using IIF() function in the sql?

I use this field in a combobox on a form, interesting thing is some of the combo's display Yes/No whilst some display it as -1/0

Thx
 

Answer:Solved: Access Query Y/N Field

7 more replies
Relevance 58.63%

This is probably so obvious that I'll feel embarassed by the answer - sorry.

Building a user interface in Access 2000 connected to SQL Svr 2000. I'm attempting to set an Event Procedure for On Enter a particular Text Box. When I constructed it Access opened the VB environment and I've set the element as below.

Private Sub OperatorName_Enter()
Dim SQL As String

SQL = "SELECT MasterOperatorDetail.MasterOperatorName" & _
"FROM MasterOperatorDetail,CollectedData" & _
"WHERE CollectedData.ClockNumber = MasterOperatorDetail.MasterOperatorClockNo" & _
"AND CollectedData.OperatorPIN = MasterOperatorDetail.MasterOperatorPIN"

' RunSQL SQL 'This is the problem area - unsure of syntax/command

SQL = OperatorName.Value

End Sub

The above tables called in the query were constructed when I set up the Project using new data. The above Select Query functions properly in SQL Server's Query Analyzer and returns expected results.

Where I need help is getting the syntax/command right.

Thoughts and/or direction(s) most appreciated. I've Googled this to no avail so the search entry I'm using must be backwards as well.

Thanks - PC
 

More replies
Relevance 58.63%

I want to do a query linking 2 tables. In one I will know exactly what text is in the requisite field. I then want to query the other table to find the field with a name that matches the text in the first table, so I can edit the value it contains. Can I do this?
 

Answer:Access--query searching for field name??

12 more replies
Relevance 58.63%

Good Day All,

I have an access database, with an "employees" table. This table contains employee IDs, first names and last names. I would like to use the form (screenshot attached below) to enter an employee id, and query the above table, using the result to populate a label (ie set the caption property). Thanks in advance to anyone who can offer any advice on how to do this.

Regards,

Milty
 

Answer:Use Access Query To Populate Field Value

man, I haven't used access in years.. there should be an onTextChanged event for the textbox in red.. use this event to update the label in blue for each char entered in the text box.
 

12 more replies
Relevance 58.63%

hi there all
the last few weeks i spend time looking for fix of this problem with no luck

every time i reinstall windows it works fine
but after i finish personalising it
the up/down control keeps modifying the value by from 1 to 3 instead from 1 to 2
this happens only when i mouse click the arrows
the up/down arrow keys on the keboard work fine and the scroll too
the mouse doesnt double click on icons or anything else
i am using windows 7 Ultimate 32bit and this used to happend back in xp too
checked the whole control panel to find a solution and my tuneup too
on my lap top it works fine dont know why i do the same setting on both

Answer:up\down control for a number field problem

Welcome to the Windows 7 Forum.
I don't really understand exactly what you mean so I have some questions.
1. What does personalizing include?
2. What up/down are you referring to? (Side Bar?)(1 to 3) ?
Thanks for filling in your specs.

9 more replies
Relevance 58.22%

Hello everyone,

I'm working on an order management database and I'm stuck on one item.

I have a form that users can fill in with purchase order data. One section of the form relates to contract review completion.

I would like to have a checkbox that the user can check when contract review is complete, but I'd also like for a completion date field to be auto-filled when the checkbox is checked.

I've tried a couple of things, both unsuccessful.

Any ideas?

Thanks.
 

Answer:MS Access - autofill date field

6 more replies
Relevance 58.22%

Is there a way to insert a date merge field where the date is spelled out (June 10, 2008) and will not change if the document is reopened, resaved, modified, etc?   Thanks for any help!

Answer:MS Word merge field for date

I'm not quite sure I understand the question, but depending on the Word document...you can use the "Insert" link from Word's toolbar to insert the date into a document...formatted to your liking.Once the document is saved...the date will always appear as you have set it.

2 more replies
Relevance 58.22%

hi, i searched in vain for an answer to this question:
when i edit an image file and resave, the date modified date does not change. since i started using computers 100s of years ago, this was automatic. how can i enable this (obvious) field? what point is there in even having a date modified field if it stays the same as the date created field? thanks!

Answer:date modification field does not change

Are you sure you have the Date Modified selected as a column to display?

Right click on the header column (has Name, Date, Type, etc.) and select the Date Modified tag.

9 more replies
Relevance 58.22%

Hello, I need help with the following:
I have a field (called Date_Time) which displays for example 1/31/2005 12:00:00 AM. I would like to run a query that converts that value to '200501' in a created field.
I tried the following below but I keep having problems.

select Account_Number, Date_Time,
concat(year(Date_Time), month(Date_Time)) as Date_Time_Modified
from table
where Account_Number = xxxx

Any insight into this would be greatly appreciated. Thank you.


 

Answer:Concatenating Date and Time Field in SQL

Just wanted to add some more examples:

Account_Number: Integer Type
Date_Time: Date Type
Date_Time: Modified: Character Type

Account_Number Date_Time Date_Time_Modified
3246 1/31/2005 12:00:00 AM 200501
4920 11/30/2008 12:00:00 AM 200811
5932 6/30/2011 12:00:00 AM 201106
... ... ....
 

1 more replies
Relevance 58.22%

Hi guys,

Something weird is happening. Some of my movie files (file type doesn't matter) became corrupted; when I play them in VLC they cause errors, they were just fine before and nothing has changed. I noticed in Explorer that these files have no Date Modified field, it's simply blank; I've never seen anything like that.

I did a chkdsk on the drive and see no errors. What could it be ?

Thanks.

Answer:Blank Date Modified Field ?

VLC should rebuild the Index, but that is a temporary fix. Depending on what format they are, there is "DIVFIX" and "Meteor MKV repair" you can try to run the faulty content through which of those is appropriate, you'll have to google it, cant remember the links.

3 more replies
Relevance 57.81%

I am using a query in Access to clean up some unneeded text from a table, then create a new table with the cleaned up text. In addition, I need to add a column to this new table that is boolean format defaulting to a "no" value. I have successfully created this query except for the new boolean column. I can get the new column to default to a value, either no or 0, but it is in text format. If anyone knows how to do this I would appreciate the help.
 

Answer:Access-Adding a field to a table with a query

This query will do it:

ALTER TABLE TableName ADD COLUMN NewColName bit
 

3 more replies
Relevance 57.81%

I have written a content management system (DW8, PHP, mySQL) which allows a user to add, edit and remove content from a mySQL database using php pages. One of the many fields in the table which contains the main page content is a field called "hits". Another field is called "pdf" and contains a link to a specific pdf file for that record.I want to be able to insert a link or button which when clicked or pressed updates the field "hits" by 1 and then takes the viewer to the pdf in the field "pdf".Anyone any ideas? It's the updating the "hits" field by 1 part that I'm having bother with.Thanks in advance.

Answer:Use UPDATE query in mysql to increase field by "1"

something like:$link_id=mysql_connect("$db_host","$db_user","$db_password");if (mysql_select_db("$db_database", $link_id));mysql_query("UPDATE tablename SET hits=(hits + 1) WHERE row_identifier=$variable");

6 more replies
Relevance 57.81%

Hi all,

Right I have a table, which is the following:
ID - Autonumber
ID2 - Number
ID3 - Number
Duplicate? - Yes/No
Name - Text

Sample Data:
Code:

ID ID2 ID3 Dup? Name
1 1 1 - Alan Smith
2 2 2 - Bob Smith
3 3 3 - Bob Smith

Data I want:

Code:

ID ID2 ID3 Dup? Name
1 1 1 N Alan Smith
2 2 2 Y Bob Smith
3 3 3 Y Bob Smith

You can ignore the ID's - they are not all the same as in the sample data, but they are irrelevant for what I need.

As you will see from the data above, "Alan Smith" is a unique name, and therefore Duplicate? is set to NO; whereas "Bob Smith" has two occurrences, so the Duplicate? is set to YES.

I know I need an append to go through each line to check if it is a duplicate. Any thoughts on how I can go about this???
 

Answer:Query to tick a yes/no box if a field contains a duplicate value [Access2010]

15 more replies
Relevance 57.81%

hi,

i have two columns date & month. I want a result of total count for the month Jan which is having blank date.

Closed DateMonth2013-01-01Jan Jan2013-01-01Jan Jan2013-01-05Feb2013-01-01Jan2013-01-01Feb2013-01-01Feb2013-01-01Jan2013-01-01Jan Jan2013-01-01Feb2013-01-01Jan Jan2013-01-01Jan2013-01-01Feb2013-01-01Feb2013-01-01Feb Jan
 

Answer:Count Number of records having blank field

Welcome to TSG muzamil. Hope you get some help and provide others with help when you get an opportunity.

What tools do you have to work with? Access?
 

1 more replies
Relevance 57.81%

I have three tables with auto number setups for the component ID , how can I make the autonumber fields for these tables start at different numbers.

What I would like is,

table 1 at zero. That one I know is default to that number

Table 2 Start at 1000

Table 3 at 3000

Thanks for the help
 

Answer:Can you change the starting number with a autonumber field

see if this helps
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209696
 

1 more replies
Relevance 57.4%

How do you insert a date field in Word 2007?

Answer:How do you insert a date field in Word 2007?

In Office 2010 at least, it should be under the "Home" or "Insert " tab. Then there should be a sub-category like "Date" or "Time".By the way. In the future, you'll get better office results here:http://www.computing.net/forum/offi...Ask and Answer. The way of learning.Dell Dimension 8300Intel Pentium 4 HT @ 3.20 GHz4 GB RAMNvidia Geforce FX5200

2 more replies
Relevance 57.4%

I receive periodic Word (.rtf) documents from a property rental agency whenever my property is rented. The document contains the dates rented, the renter's name, etc. There is a "date field" in each document which automatically shows today's date, rather than the date the doc was created and sent to me. If I want to forward the doc to the renter a week later how do I stop the date from updating when I send it and stop it from updating when the renter opens the file?
 

Answer:Solved: Stop Updating the Date Field

6 more replies
Relevance 57.4%

I have tried sending a message to a friend who, up until now has had no problem receiving. The message I am sending has no attachments. But this message has come back several times with the message:

This message has been rejected because it has
an overlength date field which can be used
to subvert Microsoft mail programs
The following URL has further information
http://www.securityfocus.com/frames/?content=/templates/article.html?id=61

but when I go to that hyperlink it is a page saying that the item 'might' have been removed.

Incidentally I have an uo to date virus checker running and have been able to send and receive emails to others since the first bounce of this email

Questions:
1. Is this a genuine response or some sort of problem at receivers end?
2. If genuine can someone explain what I might need to do, if anything can be done, at my end to get the message through. I have already tried maing the subject line smaller but that made no difference.
 

More replies
Relevance 57.4%

the answer to this might of been found with a search but hey i'm lazy today and wanted to see who the excel masters are

using excel 2007, cell a1 contains an array formula (CSE) like so - {=MAX(LEN(A2:A470000))}
cells a2 through a470000 contain dates such as
12/12/2011
etc, you get the idea, i need the formula or something else entirely to return 10 not 5

thanks in advance
 

Answer:Solved: excel length of date field

i'll answer if myself thanks to another web site, Mike these guys are slackin' 'round here it looks like

=MAX(LEN(TEXT(A2:A470000,"m/d/yyyy")))

exit celll with ctrl, shift, enter
 

1 more replies
Relevance 57.4%

Hello,Have a Excel file with multiple sheets , each sheet is identified by the persons ID numberSee attached file.Here i need to copy data from FILE1 in each sheet to FILE2As you see in file1 that data for each person is scar tared , that is date are up and down ...that data need to be moved to file2 where already date from 21 oct to 20 nov is marked.THANKS

Answer:Moving rows with Date field from one worksheet to another

Quotei need to copy data from FILE1 in each sheet to FILE2I'm wondering about the wording of the quoted part of your post.  I suspect it would be better worded like this: i need to copy data from each sheet in FILE1 in to corresponding sheets in FILE2.   Is that correct?  Are FILE1 and FILE2 names of separate Excel files?

14 more replies
Relevance 57.4%

Hello,Have a Excel file with multiple sheets , each sheet is identified by the persons ID numberSee attached file.Here i need to copy data from FILE1 in each sheet to FILE2As you see in file1 that data for each person is scar tared , that is date are up and down ...that data need to be moved to file2 where already date from 21 oct to 20 nov is marked.THANKS

More replies
Relevance 57.4%

Have sent email to several friends, there is no attachment, just relatively straight forward item with some borders and a table in the email body copy. In one case the email was bounced back as 'delivery failed' with the message:This message has been rejected because it has an overlength date field which can be used to subvert Microsoft mail programs The following URL has further information click hereHave gone to hyperlink which says 'page removed'. Other emails I sent to this friend get through. My questions are:1. I assume this is a genuine block?2. Has anyone come across this and do you know what it means?3. Is there something I can do to get my message through? [I had put a lot of work into it]Many thanks Tiggertwo using Outlook Express and windows95

Answer:Email bounced 'overlength date field'

Sounds like some sort of anti spam program which is a little too zealous. Have you tried it again to the same user?

1 more replies
Relevance 57.4%

Hello,

I have a problem with an access database report I am working on. how do I get the memo field of the report to show more than 255 characters? The data is there in the table but when I try to run the report, it cuts off or truncates the data after 255 characters. I went into the query and "ungrouped" the fields which worked to display all the data in the table, but now the query report won't display it all.... any ideas on how to remedy this?

---- Business Analyst

More replies
Relevance 57.4%

Hi guys, just wondering if you could give me a little advice. i have a report that is generated from a query and in the fields for example bank account number i need to put in a "," before it. As well as that i have a label that displays the date, i need to be able to do the same for this as well, for example ",2010-10-21". Any guidance would be greatly appreciated, thanks for your time.

,5015105015 ,2010-10-21
,6464561564 ,2010-09-14
,5464151555 ,2010-09-13
 

Answer:Solved: putting a comma in front of my query field

Which application is it?
 

1 more replies
Relevance 57.4%

I am using Windows XP and Access 2003. I have a community database with a field that has street number, street name, unit number to make it a unique field. There are times I want to sort this field by street name in alphabetical order. Is there a way to write the criteria in the query for that field so that it ignores or strips out the street number? If so, in the case of striping out the street number, can I put the same field in the same query so that it will show up in a report, but keep all the streets with the same name together? I know I do not have to include the field without street numbers in the report.

I know I probably should have kept the street number separate from the street name, but I am too far down the road to change that at this point.

I would really appreciate the help. I do not write code, but use all the onscreen Access functions.

Thanks,
OceanAndy
 

Answer:Access 2003 Query - remove part of field

15 more replies
Relevance 57.4%

I have a query that has event date (Event_Start) where I use total min and max to get the first event (First Event: Event_Start with Min selected in the total row) and last event (Last Event: Event_Start with Max selected in the total row). When I use another column to calculate the # of Days between these 2 expressions (Days Between: [First Event]-[Last Event]), I get a pop up boxes that ask for First Event and Last Event. I can just hit enter on both and still get the calculation in the "Days Between" field in the query. How do I get rid of the pop up boxes when I run the query.
 

Answer:Solved: Calculated Field in Query getting user input box

8 more replies
Relevance 57.4%

I have a field [name] that can have up to 6 words within the field ex |hello my name is Miranda|
i am trying to get a query to split into different fields by the space between the words to look like:

|Hello| |MY| |NAME| |IS| |MIRANDA|

Any Ideas i would really appreciate it!?
 

Answer:Solved: Access: spliting a field into multiple through a query

11 more replies
Relevance 57.4%

Good afternoon all,
I've succesfully used CDate to format a text field in my Access Select Query. I encounter a problem, however, when I switch the same Select Query to an Update Query.

Error message:

Syntax error (comma) in query expression 'Tbl_Inventory.[Cdate(Mid([Added],3,2) & "-" & Mid([Added],5,2) & "-12")'.

As I said, I don't understand why my syntax works fine for the Select and not for the Update. Also, the UpdateTo field is a date/time field, but I also tried making it a text field just for giggles. Neither worked.

Thanks in advance for you assistance.

Brandi
 

More replies
Relevance 57.4%

I have a field that requires a 3 number fixed length. Unforturnately, all the 0s appearing before it disappears. How can I fix this issue.

Thanks
 

Answer:Access 2003 - fixed size for field number

It depends on what you want to do with the number, if you want it to only look like 001 instead of 1 then you can just format it. But Access will store it as 1 not 001.
If you want it to be stored as 001 then it would need to be a text field, but it would then not be so suitable for calculations.
To format the Field on a Form or Report to have leading zeroes in the Form's design view click on the Field and in it's Format property type in
000
 

1 more replies
Relevance 57.4%

Hi I am really stuck on this problem can anyone help?

I have a spreadsheet with a column (D) of words. If the word in cell D3 equals say 'Fuel' I want to post a number in column A3 = "7300"

I have worked out a formula to pick up the word fuel and post it as a number but i then need to have that formula running down the whole of my column A, and would need to amend each line to change the formula. Ideally i would like the formula out of the way on the page so that I can amend it as appropriate. eg

initally I want to search for 'fuel' and put in the number '7300' into column A (on the same line) BUT then i want to be able to change the formula to look for say 'Cash' and put the number '1230' in the column A on the same line as the word 'CASH'

7300 fuel
1230 cash
7504 personal
7300 fuel
7509 purchases

Is this possible????

Thanks

Tracie
 

Answer:excel - if field contains word post number in next column

this is my guess as to what you need. You would put it in column A at the top, then use the fill handle (bottom right corner of the cell) to click and drag to copy the formula down the column.

=IF(D3="fuel",7300,IF(D3="cash",1230,IF(D3="personal",7504,IF(D3="purchases",7509,"No Match"))))

That will work for the 4 different numbers in the example, and if you have Excel 2007 or newer, you can modify that for up to 64 different results (the limit of nested IF formulas). If you have an older version of Excel, you can only work with 7 that way, if you need more it's more work. Let me know if that does it!
 

3 more replies
Relevance 57.4%

Hi All,
I need an help in creating auto number field in access database. Let me explain you further. I have an access database which 20 million records. When i am trying to add auto number field i am getting error "File sharing lock count exceeded".
Then i did some google search and got some infomation like editing the registry file , in my case its not possible due to security restrictions.
And another option of adding a code in VB immediate window also i tried but this option is also not working.

"DAO.DBEngine.SetOption dbmaxlocksperfile,25000000"
Can anyone help how to solve this issue else can you please help me on how to auto populate the numbers in a specific field using VBA codes.
Thanks in advance.
 

Answer:Auto number field in a Access database table

16 more replies
Relevance 57.4%

Hi!

I have a Word for Mac 2011 doc that will be a template for an invoice. As pictured, I have a field that I would like to serve as the Sales Order field. I'd like the first number to be 000001. What I'd like to have happen is that each time the template is opened, the value in the Sales order field increments by 1, so the next opening would be 000002, then 000003, etc.

I know I need either some VBA code or a Macro, but I'm really unclear how to do this. Also, how do I get the code to apply specifically to the cell where the sales order number is? Unlike Excel, there's no position value to the cell, like A1. Is there a way to apply VBA code or a Macro by selecting the cell somehow?

All help appreciated!

Thanks!
 

More replies
Relevance 57.4%

can anyone help me with a formula line In Crystal Reports when designing a report i need a formula that will colored the field if maximum number is exceeded.for example: if a specific field has reach a maximum number of 15, this field has to colored in red.

Answer:formula colored field if maximum number is exceeded

Although someone might come along who knows "Crystal Reports" it is a bit specialised. You might be better advised to go to their forum:http://www.crystalreportsbook.com/f...Always pop back and let us know the outcome - thanks

2 more replies