Replies

You can use the Find Duplicates Query Wizard to create a query that returns the duplicates on specified fields. To start the wizard, activate the Queries section of the Database window, click New, select Find Duplicates Query Wizard and click OK.

If you want to weed out records that are identical in ALL fields, you could create a query based on the table that returns all fields, and set the Unique Values property of the query to Yes. Change the query into a Make Table query so that you can create a new table with the unique records.

Thank you V M Hans,

Darren.

Here is a query you can use:

Code:

Sub Convert2Date()
  Dim c As Variant
  Dim oCell As Range
  Application.ScreenUpdating = False
	For Each oCell In Range("E:E,F:F,I:I,J:J").SpecialCells(xlCellTypeConstants, xlTextValues)
	  If oCell.Row > 1 Then
		oCell = DateSerial(2000 + Right(oCell, 2), Mid(oCell, 4, 2), Left(oCell, 2))
	  End If
	Next oCell
  Application.ScreenUpdating = True
End Sub

Note: you don’t have to worry about the date format. You can set the Format property in Access after importing the tables.

Cheers, Hans, that is marvellous.

Darren

Could you post a small sample workbook? Thanks in advance.

Hi Hans, columns E f I and J

Thanks, Darren.

You can create linked tables in Access that connect to each of the tabs in the Excel spreadsheet. But then you’d have to write append queries based on these linked tables to properly populate your Access tables.

Thanks Mark, that helps, I’ll need to clean the Excel files first though.

Cheers, Darren.

Let’s say that your form is named frmInput and that the text box is named txtDays.
In the Criteria line for the DDate column in the query, enter the following condition:

>=Date()+[Forms]![frmInput]![txtDays]

Thanks Hans, that simple? I feel foolish.

Regards as ever, Darren.

Access requires brackets after the function name in expressions (unlike in VBA code), so the condition would be

>Date()+14

If you omit the brackets, Date will be interpreted as a string.

Many thanks, Hans.

Darren.

The value of the toggle button is either True (if it is “down”) or False (if it is “up”) so you can set the value of the Visible property of the calendar control to the value of the toggle button.

You need to have a reference to the Microsoft Calendar Control 11.0 library (11.0 is for Office 2003, it’d be 9.0 for Office 2000, 10.0 for Office XP and 12.0 for Office 2007).

Here in a lesson! Thanks guys, I was using a button.

Cheers, Darren.

No, that color is determined by the color scheme you select in Windows (it’s the color for 3D objects in Control Panel > Display Properties > Appearance tab > Advanced).
You can display a picture on a command button, though. This could be a bitmap of a single solid color…

thanks hans

Thanks for your comprehensive replies, thankfully I had got it right. What it has made me realise however is that the issue I have is removed somewhat.

My main form is client and sub of meetings. A client is intitially assigned to one adviser and usaully that adviser looks after that client through to the end of the process, which could be many meetings. I therefore want the adviser key in the subform to auto update without having to rely on the data inputter updating. Could I put an adviser field in the client table which can be updated for the intitial assignment when that client record is first logged and use this as a reference to auto update the adviser id in the many form? Or am I bending the rules too much here?

Thanks, Darren.

Thank you Francois

Darren.

Marvellous Hans, thanks D.

Thanks for this Hans.

Darren

Hans, this nails it. tThank you.

Darren.

For sure, I have a user form that feeds into a query the selections from a combo, I have this working fine now. The part I am struggling with is how to adapt my form to allow the user to say select students who are studying maths, english but not to show any of the students studying them if studying science. I know how to word this on the criteria line of the query but not sure how to model so users can put this in from a form..

Of course it’s structured wrong! I feel so stupid not having followed basic normalisation principles, more rusty than I thought. Thanks for pointing this out.

Darren.